Forum Discussion
Solved
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 suppo...
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
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
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)), "")
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
It's resolved now, Thanks for supporting !
I attached the file in the previous revert itself. Please find the sample file.
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
Requirement:
1. Should do the dynamic subtraction in the sequence of range mentioned- Last non-empty value - Second last non-empty value.
2. If the last 2 non- values are the same, should skip the subtraction and do the return the same value of the last non-empty cell
3. As the subtraction is proceeding in a sequence the non-populated values where the lookup formula is resulting as Zero also to be ignored & only the numbers which are >0 to be considered for calculation.did you try the formula variation I posted above?
as for a file, I don't see any file attached. If the file is .xlsb please save as a .xlsx and try again as I think this serve rejects those binary (.xlsb) files
