Calculate Trend to Target for a Quarter

[EvansRJ]

Good Afternoon,

I am trying to calculate Trend to Target for Oct 1-Dec 31. So Cells A8:A99 are my dates, Cells B8:B99 are my daily sales, cell B4 has my target and I want to insert my trend in cell B6. As I enter my daily sales in B8:B99, B6 will automatically update my trend to target.

For example, if my target for those 92 days was 920 and each day from Oct 1-10 I sold 10 units, my trend should indicate I will finish the quarter off at 100% assuming the same daily sales. If I have a day at 9 units then my trend will show me just falling short.

Can anyone help me out?

 

[EvansRJ]

Not entirely sure how to delete this post but it seems I had the same question back in 2011. Thank you MrExcel for not deleting old threads!! I found my answer!

 

[EvansRJ]

Actually, simply for cosmetics, how can I edit this formula =SUM(B8:B99)/((B4/(COUNT($A8:$A99)))*COUNT(B8:B99)), so that if there is no data entered in B8:B99 the B6 field where this formula is contained will remain blank? Thanks!

 

[joeu2004]

Actually, simply for cosmetics, how can I edit this formula =SUM(B8:B99)/((B4/(COUNT($A8:$A99)))*COUNT(B8:B99)), so that if there is no data entered in B8:B99 the B6 field where this formula is contained will remain blank?

What you ask for, with some simplication to improve readability and maintainability.

=IF(COUNT(B8:B99)=0, "", SUM(B8:B99)/(COUNT(B8:B99)*B4/COUNT($A8:$A99))

Caveat: The result is not exactly "blank". It appears blank. But referencing B6 in a numeric expression might also require changes. Sometimes you will need to write N(B6) to avoid a #VALUE error.