Dynamic multiplication

[tryingtoexcelatexcel]

Hi everyone,

I have a formula question regarding how to dynamically apply promotional discounts to a table of revenue.

So what I am trying to achieve is to apply a set of discount %'s to the first 4 months of the start of a new route.

But each time the plan changes I need to manually change the multiplication formula from old start date to new start date.

I think my explanation might not be so clear so I have included another one in the sample excel sheet itself.

Thank you very much in advance!

Promotional Fare Collection.xlsx

[etaf]

where do you get the full forecast revenue from - how is that entered

you could do it with a IF (
BUT you would need the 4 months previous of zeros to work
or a flag to say when the 1st month occurred - so the date the route starts and use datedif() to calculate the month
http://www.theexceladdict.com/_t/t040303.htm
http://www.exceltrick.com/formulas_m...edif-function/

Then you should be able to enter say new columns E and F - with the revenue (full) and the start date
and use a IF() with a datediff() and IFerror() to start the forecast all automatically
how do you know if china or non-china for the different % to apply

So i have changed row 7 & row 8 in the attached file - so you add a revenue number and a date to start

=IFERROR(IF(DATEDIF($F7,G$3,"m")=0,$E7*$F$19,IF(DATEDIF($F7,G$3,"m")=1,$E7*$G$19,IF(DATEDIF($F7,G$3,"m")=2,$E7*$H$19,IF(DATEDIF($F7,G$3,"m")=3,$E7*$I$19, $E7)))),0)

[tryingtoexcelatexcel]

So the revenue is calculated automatically from other cells, I just copy and pasted values here.

The routes that belong to China I can tell from the route itself such as TAO, PVG, maybe I should add another column and label China Non-China next to each route.

I think for new routes I do have 4 months of 0's before them. TPE starts the earliest so I do have 0's for Jun to Sep.

I'm not too sure how datediff would factor into the formula. Would I be using a lot of nested If's for this formula? Would you happen to have a formula worked out for this situation by any chance?

[etaf]

I have edited my post and added the formula and examples in row 7 & 8
and attached a sample

We will have to setup differently if you are using data from other cells
What other cells do you get the revenue from ?

and in your example are you applying the % to the figures in the other cells

row 9 for example
is it 60% of 32071200
then 70% of 27489600

if so we can do that - but would need to have access to those numbers or use this sheet as it is and calculate another section

[tryingtoexcelatexcel]

Ah ok, I think there might have been some confusion. I have added another section so hopefully it is clearer.

The Ticket price from D23 - D33 is multiplied by the number of trips in the first table, so each month the revenue will be different depending on trips taken.

So we wouldnt be taking a flat monthly revenue of 16M or 40M, each month it will change depending on number of trips which varies and the ticket price which is fixed.

The idea is that the first month tickets will be sold at a promo price of 60%/70% of full price depending on destination, 2nd month of 70%/80% and etc etc until the 5th month where prices stabilize to full price. So the start of a new destination will face promo prices for the first 4 months. That is where the 60% 70% 80% 90% come in.

I hope this makes sense. I have attached the new file below.

Promotional Fare Collection v1.xlsx

[etaf]

Ok you can still use the same formula

=IFERROR(IF(DATEDIF($F7,G$3,"m")=0,$E7*$F$19,IF(DATEDIF($F7,G$3,"m")=1,$E7*$G$19,IF(DATEDIF($F7,G$3,"m")=2,$E7*$H$19,IF(DATEDIF($F7,G$3,"m")=3,$E7*$I$19, $E7)))),0)

But instead of the
$E7*$F$19

you would use your normal calc
=$D25*I7
and add in the %
=$D25*I7 * $F$19

but we would need a start date column and a china / non-china

I can change this example if that would help

[etaf]

see attached - if you check the numbers and they are OK
then its another set of IFs for china/non-china

[tryingtoexcelatexcel]

Ok, here is an updated spreadsheet. Can you change the example spreadsheet, I think it would be helpful.

Promotional Fare Collection v2.xlsx

[tryingtoexcelatexcel]

Oh wow that is quite the formula, I think I need to take some time to digest this.

[etaf]

i think this now works on your last example

=IF($D23="china", IFERROR(IF(DATEDIF($E23,G$21,"m")=0,G5*$F23*$F$37,IF(DATEDIF($E23,G$21,"m")=1,G5*$F23*$G$37,IF(DATEDIF($E23,G$21,"m")=2,G5*$F23*$H$37,IF(DATEDIF($E23,G$21,"m")=3,G5*$F23*$I$37,G5*$F23)))),0),
IFERROR(IF(DATEDIF($E23,G$21,"m")=0,G5*$F23*$F$38,IF(DATEDIF($E23,G$21,"m")=1,G5*$F23*$G$38,IF(DATEDIF($E23,G$21,"m")=2,G5*$F23*$H$38,IF(DATEDIF($E23,G$21,"m")=3,G5*$F23*$I$38,G5*$F23)))),0))

just leave the startdate blank - and that will assume the start date was before the spreadsheet and not apply any discount

otherwise just put the startdate into the cell column E
you can put a start date before the spreadsheet starts so jun 15 is the first date
if you put a startdate of 1st may 15 - then the june figure will have the 4th month 90/90% discount applied

I can help you understand the formula - where your stuck
once you have had a chance to check the values and are happy its working OK

[tryingtoexcelatexcel]

Oh wow ok, that is a big formula. I will comb through the formula and if there are parts that I might not understand then I will post here again. Thank you ETAF, that was really really helpful.

[etaf]

your welcome

its really just an
IF()
IFERROR()
DATEDIF()

the datedif(startdate, enddate, "interval")
calculates the interval between two dates - I have used "M" - so it counts the number of complete months - you will see the full details from the links I posted

so all I do is test with a a nested IF to see if the months are 0,1,2 or 3 and apply the % for the required month
the IFERROR() is when the startdate is after the enddate and will return a zero

and then the formula is repeated for a different % cell if its NOT "china"

this is the main formula
IFERROR(IF(DATEDIF($E23,G$21,"m")=0,G5*$F23*$F$37,IF(DATEDIF($E23,G$21,"m")=1,G5*$F23*$G$37,IF(DATEDIF($E23,G$21,"m")=2,G5*$F23*$H$37,IF(DATEDIF($E23,G$21,"m")=3,G5*$F23*$I$37,G5*$F23)))),0),

its just repeated to use row 38 instead of 37 for the non-china

[tryingtoexcelatexcel]

wow that is really good, datedif is really helpful thanks!

[etaf]

your welcome