Solved Need Excel formula To Return Value Based On Other Cells

September 20, 2013 at 11:27:42
Specs: Windows 7
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, 400

Any help please? I'm stumped.


See More: Need Excel formula To Return Value Based On Other Cells

Report •

#1
September 20, 2013 at 14:22:29
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.


Report •

#2
September 20, 2013 at 14:43:38
✔ Best Answer
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.


Report •

#3
September 24, 2013 at 07:06:22
A very genuine and sincere "Thank you!" for your help.

Report •
Related Solutions


Ask Question