mercredi 8 novembre 2017

SUM column multiplied by x, but only if y is less than x, if else multiply z

I have quite the tricky pickle; I have a piece of sales data which has Grand Value, Monthly Fee and Contract Term.

It looks like this.

Grand Value Monthly Fee Contract Term (months)
 $100.00     $20.00     5
 $120.00     $10.00     12
 $120.00     $10.00     24

The first thing you might notice that the last entry value looks wrong; it isn't, it is the annual value of that sale, not the total value. It is calculated elsewhere as "est revenue" but that's irrelevant to the question.

What I need to do is get an accurate view on the current years value, not the total value over x number of years.

In layman terms, the query i'd like to write is "give me the product of multiplying the Monthly_Fee by Contract_Term by 12, but if less than 12 multiply by Contract_Term instead".

Currently the best query I have is

=SUM(Data!Monthly_Fee:Monthly_Fee)*12

Which just lazily multiplies the monthly fee by 12.

Any excel masters care to help?

Aucun commentaire:

Enregistrer un commentaire