#  Other Applications & Softwares  > Outlook Formatting & Functions >  >  Setting up a trend percentage based on date

## Adash

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!

----------


## FDibbins

1st, most of your "numbers" in column E are actually text that just looks like a number (test with =ISNUMBER(E2) copied down.

Excel treats text and valuesx differently, 1 main difference is that you cannot sum text.  The reason, in row 3 for instance, is you input 100 into F3, C3 changes to 408 because excel is converting the text to a value though the addition of + (plus).  That will work the way you have structured that formula in column C, but a much more efficient formula would be...
=SUM(E2:AI2)  copied down...this wont work with text numbers.

For the Trend, if we are on day 10, but only 5 days have values in them, will the "blank" days be filled with 0 or just left blanK?

There are a number of ways of looking at this.  1 way would be to show what the daily sales need to be to real Goal, based on teh days remaining...
=(D2-C2)/(MAX(E1:AI1)-DAY(A7))

----------


## Adash

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.

----------


## FDibbins

I also noticed that all of your cells are formatted as text.  Highlight the entire range and select General.  you will need to re-enter the data you have  (if its not too much), otherwise put 0 in a cell somewhere, press CTRL C (Copy), highlight all your "text" numbers, click the Paste dropdown and select Paste Special, the click the Add option)

OK, now if you use this instead...
=(D2-C2)/(MAX(E1:AI1)-DAY(A7))
It will give you the remaining daily target to achieve goal

----------


## Adash

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  :Smilie: 

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!!!*

----------

