Forum Widgets
Latest Discussions
Transpose in arrays of n columns
Hello all and thanks in adhance for your help. I am tryting to find a formula or shortcut that would allow me to transpose arrays of n columns into matrixes of n rows and n columns, to plot the data in a different software. In this example I have two arrays, each is 3x3: The software where I need to plot, asks for three arrays of 2x3 as shown: In reality I'm working with three different softwares, the first one gives me raw data that I compute in Excel, obtaining up to 56 groups as shown in the first picture; however, the third software requests the data as the structure shown in the second picture. Any suggestions are welcome.VMelgarejoCazJan 23, 2025Copper Contributor80Views1like7CommentsCOUNT UNIQUE VALUES WHILE FILTERING
Hi, I need some help regarding my file. I have managed to count total unique values in column L which is 4,075, however if I apply filter on the dates that are not blank, it still showing total unique values of the whole column L. (screenshot only, I filtered it by date of welding.) this is the formula I use: =SUM(IF(FREQUENCY(IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""),IF(LEN(L10:L12194)>0,MATCH(L10:L12194,L10:L12194,0),""))>0,1)) Looking for some assistance. Thank youRodgie22Jan 23, 2025Occasional Reader32Views0likes2CommentsName Manager does not list all names it has remembered.
There was a table in my workbook called Category. I deleted the table, then created a set of cascading drop-down tables, which the app named as Table1 thru Table6. I then set out to rename the tables with their column headers. This worked fine except for Table1, which I was unable to rename as Category to replace the deleted table, so I renamed it as Categories. My workbook now has only the names shown in this screen shot: When I try to change the name of the Categories table to Category, the app consistently returns: Note that this set of tables is the only content of the workbook, There are no cells anywhere, and never have been, which reference the Category label. I have been programming since 1970. No compiler or interpreter I have ever worked with has ever exhibited this behavior. If I delete a named variable, the name should immediately be available for reuse. How do I work around this defect?PluschapJan 22, 2025Occasional Reader19Views0likes1CommentCountif 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 Contributor15Views0likes1CommentSearch 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 Contributor18Views0likes1CommentFrench 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 Reader6Views0likes1CommentFormula 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 Contributor60Views0likes7CommentsIf 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 Reader14Views0likes1CommentHelp 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 ZSolvedTomtomJan 22, 2025Occasional Reader29Views1like1CommentCan 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 Reader13Views0likes1Comment
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