Forum Discussion
Analysis formula based on dates - suggestions
Hi all!
I am stumped!
I have a large spreadsheet of sales figures by month for a number of years. I want to add in a formula that will calculate sales commissions. The Commissions are payable as follows :
12 months back = 10%, next 12 months = 1%
I need to be able to drag it then every month and every time a new customer is added, and I need it to only go back 24 months. Anyone got any ideas cos I cant figure a formula that will do it all!
IN the attached file, I want to put the formula into cell AA3 and then drag to each month and each client/customer (so across and down)
Please, please help!
Marion
7 Replies
- Hi Savia, thanks for the prompt reply. So sorry I forgot to say, it needs to be dynamic based on the number of months because i want to be able to drag it forward into future months and we have hundreds to lines to be analysed. so what i was trying to do is write a formula to say count back 12 months from this month (cell AA2) and multiply each 10% and then count back another 12 months from Cell AA2 and multiply each by 1% and add the 2 totals. Do you think thats possible ?
also I couldnt open your attachment - so maybe youve done this for me lol!
Thanks!
- Hi Riny, thanks for the prompt reply. So sorry I forgot to say, it needs to be dynamic based on the number of months because i want to be able to drag it forward into future months and we have hundreds to lines to be analysed. so what i was trying to do is write a formula to say count back 12 months from this month (cell AA2) and multiply each 10% and then count back another 12 months from Cell AA2 and multiply each by 1% and add the 2 totals. Do you think thats possible ?
ThanksReread your question and explanation. Perhaps this one does what you want.
=B3*10%+SUM(C3:N3)*1%
