Conditional IF() statement problem, not returning desired value

Question

Currently writing a program in excel that will return a value based on user input. The current formula has 5 different return options which are returned based on the selection of a number by the user. I use the IF() statement embedded into more IF() statements to account for multiple input options. However, when I go to enter in a number beyond the range of the first IF() statement, I am getting 0 even though it should be a different number.

For the code below, C30 is the input cell and it should return .15 if I was to enter 25.

=IF(C30<20, 0.35, IF(20<C30<40, 0.15, IF(40<C30<60, 0, IF(60<C30<80, -0.1, IF(80<C30, -0.2, 0)))))

From the logic statements, it should be returning .15, but all I am getting is 0.

Try replacing 20<C30<40 with just C30<40and replace 60<C30<80 with just C30<80. — Gravitate
– Gravitate, Commented Apr 15, 2019 at 16:03
If you go to "Formulas" > "Evaluate Formula" and step through - you should see the issue on the second or third click of "Evaluate"... — Chronocidal
– Chronocidal, Commented Apr 15, 2019 at 16:14

Scott Craner · Accepted Answer · 2019-04-15 16:02:52Z

4

Excel does not use 20<C30<40 it would be:

AND(20<C30,C30<40)

But you can shorten this with a simple MATCH and CHOOSE:

=CHOOSE(MATCH(C30,{0,20,40,60,80}),0.35,0.15,0,-0.1,-0.2)

If you really want a nested if there is no need for the extra tests:

=IF(C30<20,0.35,IF(C30<40,0.15,IF(C30<60,0,IF(C30<80,-0.1,-0.2))))

IF will resolve sequentially and short circuit as soon as it finds the first TRUE, so it does not need the other logic.

answered Apr 15, 2019 at 16:02

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin · Accepted Answer · 2019-04-15 16:03:38Z

1

The problem here is the logic that you have used to evaluate whether C30 falls within a range of numbers.

IF(20<C30<40,...) will not check whether C30 is in the range of 20 through 40.

Instead, use AND(cond1, cond2, ...) to check whether the values are within the range:

IF(AND(C30 > 20, C30 < 40), ...)

answered Apr 15, 2019 at 16:03

Martin

16.5k1 gold badge33 silver badges51 bronze badges

1 Comment

Chronocidal Over a year ago

I've always been fond of the form IF(C30=MEDIAN(20,C30,40), ...) for this

Gary's Student · Accepted Answer · 2019-04-15 16:03:31Z

0

Replace terms like:

20<C30<40

with:

AND(20<C30,C30<40)

etc.

answered Apr 15, 2019 at 16:03

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Conditional IF() statement problem, not returning desired value

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related