Forum Widgets
Latest Discussions
Countif Formula Help
I am tracking employee errors on transactions for my organization and am trying to provide calculate how many times each error has occurred over a 12-month time frame. The error reasons are programmed into the employee tracking logs and I have compiled everyone's log. All of those logs have been compiled into one giant list (about 1300 entries). They went super granular when creating these tracking logs and there are 68 different "Error Reasons" - Below is an example of a handful of them. Previously when I have done this, I have manually input CountIf formulas for each of the error codes, which is doable but very time consuming. Is there a way to get Excel to auto populate the error reasons?MgundelJan 22, 2025Copper Contributor6Views0likes1CommentSearch for Highlighted Cells
Hello, I am an occasional Excel user working on a special project where I created an Excel spreadsheet whose data consists of a large number of comments. What I am trying to do is find any cell that contains a specific word, highlight that cell, and then create a new worksheet that contains all of those cells with the word in it. The best way I thought of to do this was to conditionally format the spreadsheet containing the data. So now, every cell that has the magic word in it is highlighted yellow. How do I pull only the highlighted cells into a new spreadhsheet? Thanks in advance.SHSIMPSOJan 22, 2025Copper Contributor6Views0likes1CommentFrench keyboard accounting issue
My Excel document is refusing to format the cells of a row of numbers in French euros format. Anyone have any suggestions on how to fix this? I have contacted support and just spent an hour on this. It will not accept the commas which French format uses instead of the decimal point. Even when the version of the app is put into the French language. Format cells using French or euros do not work either. The only fix seems to be to use decimal points. But this means my software would be incompatible with the Excel file downloads taken from my French accounting system online... the idea of having to change every comma to a fullstop is frankly depressing!! There's gotta be a better way...numpkinJan 22, 2025Occasional Reader3Views0likes1CommentFormula not working - Dynamic array for subtraction
Currently I'm facing a error with the below formulas, which is giving Error "You have entered too many Argument for this function" or simply "Zero" is coming as output. Hence requesting any SME support to fix this formula and help me to make use in my spreadsheet. Below few trouble shoots done from my end before publish my concern here: 1. I have ensured there is no blank cells or without values. 2. I'm using MS-excel 365. where few functions in the formula is compatible for my version as mentioned on the internet. Formula 1: =IF(COUNTA(BD3:BO3)<1, "", IF(LOOKUP(2,1/(BD3:BO3<>""), BD3:BO3) = LOOKUP(2,1/(BD3:BO3<>""), OFFSET(BD3:BO3, 0, -1)), LOOKUP(2,1/(BD3:BO3<>""), BD3:BO3), INDEX(BD3:BO3, 1) - SUM(FILTER(BD3:BO3, (BD3:BO3<>"") * (BD3:BO3<>OFFSET(BD3:BO3, 0, -1))))) ) Formula 2: =IF(COUNTA(BD3:BO3)<1, "", LET( LastValue, LOOKUP(2, 1/(BD3:BO3<>""), BD3:BO3), PreviousValue, LOOKUP(2, 1/(BD3:BO3<>""), OFFSET(BD3:BO3, 0, -1)), IF(LastValue = PreviousValue, LastValue, PreviousValue - LastValue) ) ) Formula Purpose Intended: To dynamically subtract the values from left to right on the last 2 non-empty cells from the given range BD3:BO3 1. Running Subtraction: Subtract values of last 2 non-empty - Last non-empty cell from the above specified range, it should subtract the last 2 non-empty cell only, whenever the range sequentially get updated with values. 2. Ignore Duplicates: If the value being a duplicate/same in the last 2 non-empty cell, then the subtraction operation should be skipped & the formula should only give the same value in the last non-empty cell. Please help me to fix this, I have tried multiple measures but still the error persist . I have attached sheet as well.SolvedPravchaJan 22, 2025Copper Contributor54Views0likes7CommentsIf formula problem
Hi all! I am new to using excel so I appreciate your help in advance :) I created a spreadsheet to track multiple choice answers for a quiz. This is the formula to track if people got the answer right: =IF(F3="a",1,IF(F3="b",0,IF(F3="c",0,IF(F3="d",0,IF(F3="-",0,NA))))) However, someone answered "bd" instead of only selecting one and the formula resulted in an error. How do I correct the formula so that the answer would be "0" in that case? Thank you?emzyan0160Jan 22, 2025Occasional Reader7Views0likes1CommentHelp creating a formula
Hi! What I need Excel to do is: If the value in column A is X and the value in column B is between a certain range of numbers the answer is Y If we have the same value in column A but the value in column B is between a different range of numbers the answer is ZTomtomJan 22, 2025Occasional Reader21Views0likes1CommentCan we prevent Excel from auto-appending year?
Would appreciate it if someone could help me with this little problem: I have a spreadsheet where I do reconciliation of monthly sales, and I usually key in the dates as dd-yyy. Eg, for a payment received on 15th of January 2025 I'll key in "15-Jan" without the year. I only found out today that my December sales reconciliations have mixed dates: eg, some 9-Dec-2024 and some 9-Dec-2025 I went back to check my December-2023 sales reconciliation file and it does have mixed 2023/2024 dates as well. Other months don't seem to have this problem (I've only checked a few files). I'm guessing that: a) those that I keyed in on or before Dec-2024 will have the year as "2024", and b) those that I keyed in after 1-Jan-2025 got tagged as "2025" - cos I remember working on rows 8/14/21/54 in the above screenshot in January of this year. I suppose keying in the full date (day/month/year) will resolve this problem, but if I don't intend to use this data in calculations is there a way to stop Excel from auto-completing the year to the entry itself? Formatting it to "dd-mmm" looks fine on my end, but when I upload the file somewhere and another user downloads the file, somehow that person ends up seeing the mixed year (which was how I found out about this issue). And that's kinda annoying because the other user flagged it as wrong data entry.TjinJan 22, 2025Occasional Reader4Views0likes1CommentExcel 365 Focus Cell - "Show Auto-Highlight" feature is disappeared
Hi everyone, I recently update the Excel 365 to and I found the "Show Auto-Highlight" feature is disappeared. From Microsoft website: Increase ease of navigation with Focus Cell in Excel | Microsoft Community Hub, this feature is very handy when using the find fuction. how can I get this function back? Thank you!joeyc380Jan 22, 2025Copper Contributor4Views0likes1CommentExcel Displays Dates as Serial Numbers on Referring to a Cell, but Google Sheets Handles It
I’m encountering an issue with date formatting between Excel and Google Sheets: When referring to a cell with a date in Excel, it displays as sequential serial numbers instead of the expected date format. If I open the same file in Google Sheets, it correctly recognizes and formats the dates. Strangely, when I download the file from Google Sheets and reopen it in Excel, the dates display correctly in Excel. Why is this happening, and how can I ensure Excel consistently interprets these values as dates? Any tips for resolving this issue would be greatly appreciated at code level ( Ruby on Rails ).KarthikCAS109Jan 22, 2025Occasional Reader21Views0likes5CommentsNeed to return col A if another col contains the text in col B (array) anywhere
So, here's my array in columns A and B: Column L has a journal description that COULD contain one of col B descriptions. So, column L will have things like "3M LO(a)N 2024.12" OR "3M LO(a)N 2024.11", etc. But they could also have "TRU UP COLORADO INTEREST #3126" - basically, the description in col B could be anywhere (together or separated) in Col L. What I want to do is, in Column C write a formula that will look at column L and compare it to the array in columns A & B, and then return column A if the value in col B is found. So, for "3M LO(a)N 2024.12", col C would return "1". For "TRU UP COLORADO INTEREST #3126", return "5". The difference being "3M LO(a)N" is found in the same sequence as it is in the array, but "COLORADO #3126", though contained in col L, the two parts are separated. I'd like to leave these descriptions as they are to help in identifying if/when I need to change the array, but if it's too difficult, I could just use "#3126" as the description in col B and add a vehicle description in another column. The array is named "CATEGORIES" to ease formula creation. Thanks for any help in advance. *** apparently the full word LO AN is not allowed in the text. So, LO(a)N it is.SolvedRandomPanda1933Jan 22, 2025Copper Contributor84Views0likes5Comments
Resources
Tags
- excel42,052 Topics
- Formulas and Functions24,376 Topics
- Macros and VBA6,335 Topics
- office 3655,906 Topics
- Excel on Mac2,608 Topics
- BI & Data Analysis2,314 Topics
- Excel for web1,867 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,601 Topics