Forum Discussion

v0elker's avatar
v0elker
Copper Contributor
Jan 04, 2023

Line graph showing quarterly target and an adjusted target HELP

Maybe a stupid question, but I need help figuring out how to add a Line to my graph in Excel to represent an adjusted target that changes dependent upon the actual number completed.

 

The adjusted target line would display how many items must be completed to still reach the goal given the actual amount already completed.

For example:

They must publish 313 items a quarter to meet the goal of 2500 in two years. If they only published 50 the first quarter that adjusted quarterly target would increase and vice verse. 

Charting
excel
Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Jan 16, 2023

    v0elker The calculations can be set up as shown:

    The Next Qtr Target column is not required, but makes the calculations more obvious.  (Technically, F3 is not an "adjusted" target as C2 is by definition zero, but otherwise…)

    Formulas for columns D and E:

    Remaining (D3):
=MAX( 0, D$2-SUM(C$2:C3) )
Next Qtr Target (E3):
=IF(ISBLANK(C3), E2, CEILING.MATH( D3 / (ROW(D$10)-ROW(D3)), 1 ) )

    Column F formulas just copy the value from column E in the prior row.

     

    I won't address adding this data to a line graph.

Resources