Previous Topic Next Topic

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
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.
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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)
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses