Excel help!
Closed
Jo
-
May 31, 2010 at 02:14 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 5, 2010 at 10:26 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 5, 2010 at 10:26 AM
Hi. I have a formula that relies on a series of monthly updates all being populated before it will produce an outcome. What this means is that even if I have 2 out of 3 of the values that I need, the formula will not produce an answer until the 3rd value is available. I would like to re-construct the formula so that if there are 2 out of 3 of the values available (any 2 of them) then the remaining cell gets populated with the 'last value' that it was (ie from the row above), so that the final formula can produce a number.
The formula I currently have is:
IF(AI370=" "," ",IF(G370=" "," ",IF(F370=" "," ",AI370*$AN$3+G370*$AN$4+F370*$AN$5)))
So in this instance, if I have any 2 of AI370 / G370 / F370, then I would like the formula to act use the last value of the last cell. I.e. use AI369 instead of AI370 - if AI370 was the only value that was missing.
The formula I currently have is:
IF(AI370=" "," ",IF(G370=" "," ",IF(F370=" "," ",AI370*$AN$3+G370*$AN$4+F370*$AN$5)))
So in this instance, if I have any 2 of AI370 / G370 / F370, then I would like the formula to act use the last value of the last cell. I.e. use AI369 instead of AI370 - if AI370 was the only value that was missing.
Related:
- Excel help forum
- Excel mod apk for pc - Download - Spreadsheets
- Number to words in excel - Guide
- Kernel for excel - Download - Backup and recovery
- Gif in excel - Guide
- Excel marksheet - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 5, 2010 at 10:26 AM
Jun 5, 2010 at 10:26 AM
After correction:
Try this
=IF(AND(TRIM(AI370)="",TRIM(F370)="",TRIM(G370)="")," ", INDEX(AI$1:AI370,MATCH(9.99999999999999E+307,AI$1:AI370)) * $AN$3 + INDEX(G$1:G370,MATCH(9.99999999999999E+307,G$1:G370)) * $AN$4 + INDEX(F$1:F370,MATCH(9.99999999999999E+307,F$1:F370)) * $AN$5)
Try this
=IF(AND(TRIM(AI370)="",TRIM(F370)="",TRIM(G370)="")," ", INDEX(AI$1:AI370,MATCH(9.99999999999999E+307,AI$1:AI370)) * $AN$3 + INDEX(G$1:G370,MATCH(9.99999999999999E+307,G$1:G370)) * $AN$4 + INDEX(F$1:F370,MATCH(9.99999999999999E+307,F$1:F370)) * $AN$5)