Automatic Price Calculation

Question

I'm trying to find a formula i can use which will automatically calculate a price based on certain day rate criteria.

My pricing structure is as follows:

0-5 days = £35 a day

6-10 days = £25 a day

11-15 days = £15 a day

So for up to 5 days the rate will be calculated at £35 a day, but if customer books 7 days for instance the rate will be calculated at £35 for 5 days and £25 for the remaining 2 days.

Hope that makes sense?

Is there any easy way to achieve this?

Thanks

Chris

Answer 1

You can use:

=SUMPRODUCT(VLOOKUP(SEQUENCE(A5),A2:B4,2))

Where A5 is the number of days and A2:B4 is the following lookup table:

Number	Value
0	35
6	25
11	15