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