Calculations for Quota by month

[Jim]

Hello,

Thanks for the help.

- In Column A I have Salespeople names listed (row 1 is the header)
- In column's B through M I have the months of the year, starting with
October in column B (fiscal year)
- Column N is blank
- Column O is the cumulative total
- We will talk about Columns P & Q in a moment.
- Column R lists the salespersons yearly quota
- Column S lists the salespersons percent to quota (for the year)

Question: In column P I would like to list the salespersons year to date
quota. For example, if his quota is 60k for the year, that would be 5k a
month. We are in December and the fiscal year started in October, so I would
like Column P to show 15k. In January 20K, etc...

The best solution: is a formula that will show me by day the growing quota
so I can have an accurate percentage of where that salesperson is. Example:
5K for October plus 5k for November plus 3542 (22 days of quota), then Column
P will show 13,542... but tomorrow Column P will show 13,703

Thanks in advance for any help you can offer. And if you have time to
provide formula's for both, I would certainly appreciate it.
Jim

 

[Fred Smith]

Your quota-to-date would be:
=o2/365*(today()-date(2009,9,30))

Or, put your fiscal year end in a cell and use it in the formula.

Also, blank columns (and rows) make it more difficult to navigate around a
spreadsheet. You'd be better off to widen column O and delete column N.

Finally, I don't see where you are asking for a second formula.

Regards,
Fred