Forum Discussion

Copper Contributor

Feb 04, 2020

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 f...

sample.xlsx9 KB

BI & Data Analysis

excel

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Feb 04, 2020

marionob

Could perhaps be like this:

=SUM(B3:M3)*10%+SUM(N3:Y3)*1%

marionob

Copper Contributor

Feb 04, 2020

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 ?
Thanks

Riny_van_Eekelen
Platinum Contributor
Feb 04, 2020
marionob
Reread your question and explanation. Perhaps this one does what you want.

=B3*10%+SUM(C3:N3)*1%
Riny_van_Eekelen
Platinum Contributor
Feb 04, 2020
marionob
Understood that, but assumed that you would also expand the width of your table. April 2020 calculates for 10% of April 2019 to March 2020 plus 1% of April 2020 to March 2021. For May 2020 it then shifts everything by one column to the right, but then you would have to add a column for April 2021.

So, let me return the question. Which calculation would your want to make in AB3?
- marionob
  Copper Contributor
  Feb 04, 2020
  iRiny_van_Eekelen , this is a typical example of "over thinking" something!! I was looking at all sorts of complicated formulae analysing dates, etc when actually, you are right - all I needed was a simple formula I could drag!! I feel like such a dimwit lol!! Thanks a million for bringing me back down to earth lol! 🙂