nested if with 4 price points

Hi All,

I am trying to run a nested IF so that the price for a product is zero if not launched, then it is full price for 2 months followed by half price for 3 months, thereafter it falls to minimum price, and stays at this price until it the product ceases. Prices ar $1.99 full price, $0.99 half price and $0.69 thereafter.

The code I enter (and tried different permutations) gives me full price followed by minimum price, but I cant get it to give me the mid price. I know this maybe an obvious error, but I am now going round in circles on this one.

The code I have used is :=IF(H39>5,$S1,IF(H39>0,$Q1,IF(H39>3,$R1,"0")))

H39 is the month number and increases each month with month 1 being launch month.

If anyone can correct the above code, I would be very grateful.

Regards

NoeBol

Almir's picture

nested if with 4 price points

Here it is:
=IF(H39<1,"0",IF(H39<3,$Q$1,IF(H39<6,$R$1,IF(H39>5,$S$1,"0"))))

Logic behind:
1. First, it checks if month number is blank or zero and assigns "0"
2. If there is a number greater than 0, it check if it is 1 or 2 and assigns max price - first two months
3. if it is greater than 2, it checks if it is 3, 4 or 5 (following three months) and assigns mid price,
4. Finally, it check if price is greater than 5 and assigns 0,69 or otherwise "0".