# Excel help! [Closed]

-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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.

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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)