YTD Goals in Excel

[OLIINLA]

Hi all - so I need some help - Attached (I hope)

Goals are laid out in C2:N2
Annual Goal is SUM in O2

Actual results are listed in C4:N4 (updated as the month closes)
"forecast" Formula in P4 is =AVERAGEIF(C4:N4,"<>0")*12

But the employee has actually exceeded her goal every month - so it shouldn't be 98% to target it should be +100% tracking

I know it shouldn't be averaged...but I cannot think what formula I need to use instead.....any help?

Capture.PNG

[Pete_UK]

Maybe you need a formula like this:

=SUM(C3:N3)/SUMIFS(C2:N2,C3:N3,">0")

so that you are comparing like with like (i.e. you are only adding the row 2 values where there are corresponding values in row 3).

Hope this helps.

Pete

EDIT: Not sure if that should be row 3 or row 4 - what's the difference?