Forum Discussion
Pravcha
Jan 20, 2025Copper Contributor
Formula 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.
looks like you have lots of ZERO values that I'm guessing you want to ignore also? Maybe try:
=IFERROR(LET(range, BD3:BO3, filled, FILTER(range, range<>0), last, TAKE(filled, , -1), previous, TAKE(DROP(filled, , -1), , -1), IF(last=previous, last, previous-last)), "")
How about
=IFERROR(LET(range, BD3:BO3, filled, FILTER(range, range<>""), last, TAKE(filled, , -1), previous, TAKE(DROP(filled, , -1), , -1), IF(last=previous, last, previous-last)), "")
Same as Dynamic array not working
- PravchaCopper Contributor
Thanks! HansVogelaar for trying to help me.
I still couldn't get the output expected after applying the above Formula, the output is "0" only returning . PFB
I'm kindly attaching the spreadsheet for your reference to ease the intention for supporting me. Please help me to get a working formula.
Sheet name: Dump paste
Range experimented: BD3 to BO3 on getting the output at range BP3.
Excel version: MS-Excel 365 updated & compatible for using advance functions
Trouble shoots tried:
1. Ensured No empty values at the given range
2. Number format is in "General" only.
3. Other simple formulas working in the experimented range BD3 to BO3- m_tarlerBronze Contributor
looks like you have lots of ZERO values that I'm guessing you want to ignore also? Maybe try:
=IFERROR(LET(range, BD3:BO3, filled, FILTER(range, range<>0), last, TAKE(filled, , -1), previous, TAKE(DROP(filled, , -1), , -1), IF(last=previous, last, previous-last)), "")