Formula for calculating difference & % Achievement between Fixed value and running values

[arnabnestle]

Hi All,

How to derive the formula to calculate the % Achievement when the target cell is fixed but achievement is updated every week in a different cells.

Second, In case the cell value is Zero how to structure the formula for finding the Balance.

Attached workbook.

Rgds
Arnab

[Glenn Kennedy]

In I2, copied down:

=C5-LOOKUP(2,1/($D5:$H5>0),$D5:$H5)

and in J2:

=(C5-I5)/C5

[arnabnestle]

Thanks Glenn. How to ignore #N/A in case value at column C is Zero.

[samba_ravi]

I5=if(c5,c5-lookup(2,1/($d5:$h5),$d5:$h5),"")
j5=iferror(1-i5/c5,"")

[Glenn Kennedy]

What version of Excel are you using? Your profile says 2007, but you attached an out-of-date.xls file (2003 and earlier).

[arnabnestle]

Thanks Glenn. That was so good of you.

Regards
AB

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[arnabnestle]

Thanks Ravi