Forum Discussion
Pravcha
Jan 20, 2025Copper Contributor
Dynamic array not working
Good Morning all,
Currently I'm facing a error with the below formulas, which is either giving "You have entered too many Argument for this function" or simply "Zero" coming as output. Hence requesting group members support to fix this formula and help me to make use in my spreadsheet.
Below few trouble shoots I have done from my end before writing it here.
1. I have ensured there is no blank cells or without values.
2. I'm using MS-365. where few functions (dynamic array functions, LET functions) in the formula is compatible only with MS-office 365 or excel >21 version as mentioned on the internet .
Different type of revised formula:
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 output doesn't persist .
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)), "")