Formula based on varaibles

One way:

Formula:

Please Login or Register  to view this content.

Regards, TMS

Here's another, just a little off the beaten path

=CHOOSE(B5+1,140,CHOOSE(B6+1, 100,200), CHOOSE(B6, 200,340))

Nevermind, thought it was just for the 2 nights. Thinking....

You're welcome. Thanks for the rep.


Ah, but now you're asking a different question So, what if they stay 16 nights and don't play golf? How do you apply the discount(s)?

Interesting that two nights plus one round costs the same as one night plus one round ... so, given you have the time, you can have a free night away. Is that intended?

You need to define some rules ... no idea what they might be like. In simple terms, you could just multiply the number of nights by the room rate and add the number of rounds by the golf rate. That's your full/maximum rate. But how do you then discount it?

You could have a matrix ... with golf rounds across the top (first row) and nights down the side (left hand column) and then use INDEX/MATCH/MATCH to pick a combination. Bit of a pain for say 20 nights by 20 rounds ... but you only have to set it up once.

Regards, TMS

Not enough information.
With 1 night rnd of golf is 100, with 2 nights, second rnd of golf is still 140.
with 3 nights would first 2 rds of golf be 100 and 3rd be 140?

see the example attached

So, if you stay three nights you get a free round of golf whether you want one or not? I'd rather have a discount on the room rate

I think you put the wrong value into 3 rounds, 2 nights. It should be 380. Otherwise that round of golf costs 60 and the next costs 220 (Avg = 140). The formula for B7 is

=B5*100+B6*140-INT((B5+1)/2)*140

Credit to ChemistB for the Math

See the example

Regards, TMS

Good for you. Let us know how it goes