Setting up a trend percentage based on date

Hello. I have a simple task that I can't figure out as I'm not that experienced in EXCEL when it comes to this.

I attached the excel sheet. I need the "TREND" tabs say the trending percentage based on what day it is in the month.

For example:

The goal for Person A for the month is $2,006 and there's 31 days in this month. So to trend 100% to goal, there needs to be around $64.71 in revenue daily. If the MTD for Person a would be $1,358.91, then the TREND should say 100% as it's the 21st of the month, so $64.71 (daily goal) x 21 (number of days) = $1,358.91. If the revenue for the MTD is more different, the TREND needs to fluctuate to reflect the correct trend.

I hope I simplified this, thank you for any help!

That makes much more sense, I was wondering why the SUM formula was not working when I added numbers into the days.

The "blank" days will be filled with 0. I was planning on adding some a another box with names that shows what the daily sales would need to be to hit goal. This daily goal would need to include the deficit if the previous days goal was not met.

But the issue is that when the formula spits out the daily goal, it does not take into account the days remaining.

I attached what exactly it should look like, if you can make it work I'll Paypal you $10

Here are the steps:

• I enter the persons monthly goal in the blue cell
  Each day I update each persons daily performance in the E:AI boxes.
  July has 31 days and person A has a goal of $2006. Meaning every day he has to sell minimal $64.71 to be at 100% by day 31

I put a 10-day performance sample where person A on day 10 is at $453, but should have been at $647.10.

So cell C14 should show his deficit of $194.10 and his trend in cell B2 should be 70% because the date is set to July 10, and he's only 70% of his goal.

So his next days goal needs to include his deficit split over the remaining days on top of the standard daily goal.


If you can really help me with this I will paypal $20!!!