Hello! I am trying to create a formula that will give me a resulting number depending on where it falls between three cells (with number values). 1)If value is less than A result is 0

2)If value is >=A, but less than B result is 200

3)If value is >=B, but less than C result is 300

4)If value is >=C, 400Any help please? I'm stumped.

This is just a posting tip.... When you ask for help in a forum such as computing.net, please try to use a subject line that is relevant to the contents of your post.

If everyone used a generic subject line such as "Need Excel Formula Help" we wouldn't be able to tell one thread from another. Imagine an entire forum full of threads like:

Excel formula help please

Help With Excel Please

Please Help With Formula

etc.I have edited your Subject Line to be more relevant to your question. Please try to follow this advice in the future...it will make this forum a lot more efficient. Thanks!

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You didn't say where your "value" was, so I assumed everything is in Row 1, with your "value" in D1. An IF statement has this syntax:

=IF(logical_test, value_if_true, value_if_false)

A Nested IF is an IF statement where the value_if_false is another IF statement:

=IF(logical_test, value_if_true, IF(logical_test, value_if_true, value_if_false), etc.)

An AND statement compares multiple arguments and returns TRUE only when all arguments are TRUE.

Combining the AND statements with a Nested IF, we get this:

=IF(D1<A1,0,IF(AND(D1>=A1,D1<B1),200,IF(AND(D1>=B1,D1<C1),300,400)))

Note that we don't need to check for D1>C1 (400) because the only way we get that far is if everything before that is FALSE. An IF stops checking when a value_if_true is returned so the formula above will only return 400 if everything before it FALSE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A very genuine and sincere "Thank you!" for your help.

Ask Your Question

Weekly Poll