HELP PLEASE.

I need to write an IF statement to to determine the discount given if a client pays his fees in advance. The raw data is:

<7 day no discount

7 - 13 days 5% discount

14 - 20 days 8% discount

21+ day 10%I have tried to write the statement as such

=IF(H3,<6,0;IF(>=7<=13,.05,IF(>=14<=20,.08;IF(>21,.1))))but to no avail.

Could you please help

Jen

There are 2 problems with the construction of your If statement. First, the syntax of an IF statement is:

IF(logical_test, value_if_true, value_if_false)

So, when writing a Nested IF such as yours, the second IF has to be the value_if_false for the first IF since the second IF is what you want Excel to look at if the value of the first IF is FALSE, and so on.

As an example,

=IF(A1<10,"A1<10",IF(A1>20,"A1>20","10<A1>20"))Second, if you want to check if a value is between 2 numbers, you have to use an AND statement to get a TRUE or FALSE.

=AND(A1>=1,A1<=10)will be TRUE for values from 1 to 10.So combining both of those concepts we get:

=IF(H3<7,0,IF(AND(H3>6,H3<14),0.05,IF(AND(H3>13,H3<21),0.08,0.1)))Hope that helps.

Thank you for your input...I found it very helpful and beneficial to my understanding of excel.

Glad to have helped. Thanks for the feedback.

By the way, if you want to learn more about AND formulas and nested IF statements, please visit www.excelinfinance.com

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History