percentage value depending on sale date

[Luzense]

Hello Everyone...

I need help to sort out the following, worksheet attached.

I have a sheet with a sales report but I need to add a percentage discount which depends on booking sale date. For example, all bookings are for the 23.06.2015 but the discount varies according to sale range period.

Any help is welcome.
Thank you

[djapigo]

Try this on cell Q8...

=VLOOKUP(B8,$B$40:$D$42,3,1)

Copy down to the rest of the cells...

Repost: didn't see the errors...

Try this instead...

=IFERROR(VLOOKUP(B8,$B$40:$D$42,3,1),0)

If you have an old Excel version, try this...

=IF(ISNA(VLOOKUP(B8,$B$40:$D$42,3,1)),0,VLOOKUP(B8,$B$40:$D$42,3,1))



PS: "0,00" is text... you have to change this to just 0 (must be a number, no quotes)

[skywriter]

You can try this formula for Q27 and copy down.

Please Login or Register  to view this content.

[onroute]

Hi,

First I moved your discount table matrix to W1:Y5

Then changed your discount column formula in Cell Q8 to: =SUMIFS(Y:Y,W:W,"<="&B8,X:X,">="&B8)

[Luzense]

Hi djapigo,

thank you for trying to solve my problem but, unfortunately, there is still a problem. Perhaps I was not clear before, so, here another try....

For example, I have 3 bookings arriving on 23Jun2015 but the discount varies due to the actual booked date.

If booked between dates 01Jan2014 and 14Feb2015 = discount should be 10%
If booked between dates 15Feb2015 and 27Mar2015 = discount should be 15%
if booked between dates 28Mar2015 and 15Apr2015 = discount should be 20%

So, basically the arrival day is on 23Jun2015 for all 3 bookings but the discount should change according to actual booked date.

Many thanks

[Luzense]

Hi Skywriter

thank you for trying to solve my problem but, unfortunately, there is still a problem. Perhaps I was not clear before, so, here another try....

For example, I have 3 bookings arriving on 23Jun2015 but the discount varies due to the actual booked date.

If booked between dates 01Jan2014 and 14Feb2015 = discount should be 10%
If booked between dates 15Feb2015 and 27Mar2015 = discount should be 15%
if booked between dates 28Mar2015 and 15Apr2015 = discount should be 20%

So, basically the arrival day is on 23Jun2015 for all 3 bookings but the discount should change according to actual booked date.

Many thanks

[djapigo]

Hi Luzense,

I believe the formulas are looking at Column B (Bkg Date)... we didn't use the Arrival Date at all.

[skywriter]

Hi Luzense,

I believe the formulas are looking at Column B (Bkg Date)... we didn't use the Arrival Date at all.

I agree. I tested mine with the three examples and came up with the same numbers. Looking at your VLOOKUP formula I'm sure your numbers matched also.

[Luzense]

Hi djapigo,

Thank you for trying to help but only fuctions partialy.
Thanks anyway

[Luzense]

Hi skywriter,

Thank you for trying to help but only fuctions partialy.
Thanks anyway

[skywriter]

Hi skywriter,

Thank you for trying to help but only fuctions partialy.
Thanks anyway

So if you want further help then you have to help us to help you.
Your comment has no detail as to what the issue is.

You made this comment in Post #6.

If booked between dates 01Jan2014 and 14Feb2015 = discount should be 10%
If booked between dates 15Feb2015 and 27Mar2015 = discount should be 15%
if booked between dates 28Mar2015 and 15Apr2015 = discount should be 20%

Both of our formulas do that.

You also made this comment.

So, basically the arrival day is on 23Jun2015 for all 3 bookings but the discount should change according to actual booked date.

As we replied, we understand that and if you look at our formulas they don't use the arrival dates.

I tested my formula against all the data that you provided and it was accurate. The only place the formula doesn't work is you have booking dates from 2014 and you haven't accounted for them in your discount table.
djapigo's formula addresses that issue by giving you 0 in those cells.
So if you care to tell us what the issue is we can probably help you.
From what I can see we've done everything you asked for so now you have to explain to us specifically what the issues are.

[Luzense]

Skyriter,

The discount value/formula is in fact correct and when "booked date" is not in the range it should be 0%. All ok here.
The problem is that there are 3 possible discount value depending on "booked date". Even though that the arrival day is on 23.06.2015, the discount should vary due to the booked date.
Example 1: booked on 17.01.2015; arrival day 23.06.2015; discount should be 10%
Example 2: booked on 15.02.2015; arrival day 23.06.2015; discount should be 15%
Example 3: booked on 28.03.2015; arrival day 23.06.2015; discount should be 20%

Thanks again for the help. If not possible, thats ok anyway, I will have to do it manually.

[skywriter]

Well this is getting us nowhere. You still don't give a specific example of where it doesn't work and you keep ignoring the fact that we tell you we understand that it's based on the booking date. Okay, we get that. So down below i have attached your original sheet. In cells T8:V22, is my formula in the first column and both of djapigo's formulas in the next two columns.
Also down next to your three examples that you had color coded are again my formulas with djapigo's formulas next to them. Those all match your numbers and the other formulas up top are working correctly also. I have manually looked at the booking date figured out which is the correct discount for each and verified that all the formulas are indeed returning the correct results.
Specific examples cells T8,U8,V8 are all returning a discount of 10% based on a booking date of Jan. 27, 2015 which according to your table is the correct discount. Cells T20, U20, V20 all returning a 20% discount based on a booking date of March 28, 2015 which again is correct according to your table.

If this is an issue with your version of Excel then I would think 1 of the 3 versions of the formula must work for you.

[Luzense]

Hi Skywriter,

I am sorry if you think that iam ignoring your comments or help.
I have now attached the worksheet with an example of my problem. if you look at the green line,
you will see that if the booking is done after 15.04.2015, in this example is 20.04.2015, there should
not be any discount, as the offer ends on 15.04.2015.

ThanksThomasCookSales 2015 - Copy-3.xls

[Glenn Kennedy]

I haven't read ALL of the posts - but isn't this what you wanted?

[Luzense]

Hi Glenn,

I am afraid but thats not all.

If you wouldnt mind, please have a look at my last post today at 11:15am.

Thank you

[Glenn Kennedy]

Yes, but you'll see that I put the formula into cells Q27, Q31 & Q35 and they return the discount that you specified: 10, 15 & 20%.

The formula is also in Q8-Q22 and there, too, it seems to be giving the correct result.

As I read your 11:15 am post - that is what you had asked for. If not, please take a deep breath and try to explain again...

=IF(ISERROR(VLOOKUP(B8,$B$40:$D$42,3,TRUE)),"",VLOOKUP(B8,$B$40:$D$42,3,TRUE))

[skywriter]

Hi Skywriter,

I am sorry if you think that iam ignoring your comments or help.
I have now attached the worksheet with an example of my problem. if you look at the green line,
you will see that if the booking is done after 15.04.2015, in this example is 20.04.2015, there should
not be any discount, as the offer ends on 15.04.2015.

ThanksAttachment 387145

Okay I see you have taken the file I sent you which is working fine and changed some dates and are now saying look it doesn't work. It works fine and as I told you in a post earlier, you are going to get errors because you have booking dates in 2014 and your discount tables doesn't take that into account. Now you are changing dates in the Excel workbook so that they are after the dates in your discount table and saying look the formulas don't work. The problem is your discount table, not the formula. Change your discount table, make sure the formulas reference the new discount table and it will work fine. If you decide to change dates or discounts, change the table.

1/1/1900 12/31/2014 0%
1/1/2015 2/14/2015 10%
2/15/2015 3/27/2015 15%
3/28/2015 4/15/2015 20%
4/16/2015 12/31/2015 0%

[djapigo]

Hi Luzene,

I now see the problem... our formulas rely on the booking date being "greater than or equal to, but not greater than the next date" in the lookup table.

One way to correct this is to change your date lookups for all dates possible...

Instead of just the 3 dates...
Booked date between Disct
1/1/2015 2/14/2015 10%
2/15/2015 3/27/2015 15%
3/28/2015 4/15/2015 20%

You should change it to 5 dates, include 2 "bookend" dates with 5% discount (in bold)...
Booked date between Disct
1/1/1900 12/31/2014 0%
1/1/2015 2/14/2015 10%
2/15/2015 3/27/2015 15%
3/28/2015 4/15/2015 20%
4/16/2015 12/31/2100 0%

This will insure that every date is covered... just change the ranges to this new 5 row table and everything should be good...

You can then remove all the IFERROR statements from my formula...

Hope this makes sense.

[skywriter]

1/1/1900 12/31/2014 0%
1/1/2015 2/14/2015 10%
2/15/2015 3/27/2015 15%
3/28/2015 4/15/2015 20%
4/16/2015 12/31/2100 0%

This will insure that every date is covered... just change the ranges to this new 5 row table and everything should be good...

You can then remove all the IFERROR statements from my formula...

Hope this makes sense.

I'm not sure how this is any different than what I already said in my post. I also included a file with the table you copied form my post.
The 12/31/2100 does nothing, the 4/16/2015 is the only date that matters. Any book date after that is going to default to that line.

[djapigo]

Sorry, Skywriter...

I didn't see your reply... I didn't see the thread go to a second page...

But it's good to know that my way of thinking was similar to someone else...

As for 12/31/2100... I just wanted to point out that "all" dates had to be covered...

Great job! I hope this will for for Luzense...