Excel help! [Closed]

Report
-
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.

1 reply

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)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!