Greater than or less than with 3 tests

October 2, 2015 at 05:53:00
Specs: Windows 7
I need help writing a formula that returns the following:
if E5<56000 then return "SMALL
if E5 >56000 but < 125000 then return "Medium)
if 125000 or higher then return "LARGE"

See More: Greater than or less than with 3 tests

Report •


#1
October 2, 2015 at 06:25:32
Something like this may work

=IF(E5<56000,"SMALL",IF(AND(E5>=56000,E5<125000),"Medium",IF(E5>=125000,"Large")))

message edited by AlwaysWillingToLearn


Report •

#2
October 2, 2015 at 08:22:37
You can do this with only two tests:

=IF(E5<56000,"Small",IF(E5>=125000,"Large", "Medium")))

Since processing stops once we hit a True statement:

First check the Less Than number for Small
Then check the Greater Than or Equal number for Large
What's left has to be Medium

MIKE

http://www.skeptic.com/


Report •

#3
October 2, 2015 at 12:00:23
But wait...

The OP didn't tell us what he wants returned if E5 equals 56000.

message edited by DerbyDad03


Report •

Related Solutions

#4
October 2, 2015 at 12:16:33

Guys, thanks for helping me out, here are my responses (still need help)

AlwaysWillingToLearn - this returned LARGE for all values
mmcconaghy - can you write the formula for me (I can't figure it out)
DerbyDad03 - you are right I did't specify, here is the new criteria:
if E5= or <56000 then return "SMALL
if E5 >56000 but < 125000 then return "Medium)
if 125000 or higher then return "LARGE"


Report •

#5
October 2, 2015 at 12:26:09
I did post the formula.
This one is modified with your new criteria:

=IF(E5<=56000,"Small",IF(E5>=125000,"Large","Medium"))


DerbyDad03

In the OP's original request he wanted:

if E5<56000 then return "SMALL
if E5 >56000 but < 125000 then return "Medium)
if 125000 or higher then return "LARGE"

The first test for Small is Less Than 56000
The second test is Greater Than 56000
Which, by process of elimination makes Equal to 56000, Medium.

MIKE

http://www.skeptic.com/


Report •

#6
October 2, 2015 at 12:35:30
mmcconaghy - it returns LARGE for all values

Report •

#7
October 2, 2015 at 13:16:57
Works fine on my machine.

Make sure that cell E5 is formatted as NUMBER, not TEXT
If you enter a Number and it stays Aligned to the Left inside the Cell, then it's TEXT.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#8
October 2, 2015 at 14:01:08
mmcconaghy - it worked I didn't realize that I was missing zeros as I needed millions not thousands. It is working perfectly, thanks for the help :-)

Report •

#9
October 2, 2015 at 15:10:20
Can you help me write this formula:
If I3 is < 30% then J3=0%
If I3 is between 30% and 39% then J3=8%
If I3 is between 40% and 49.9% then J3=16%
If I3 is > 50% then J3=25%

Report •

#10
October 2, 2015 at 16:04:05
Can you help me write this formula

Try writing it yourself, best way to learn is to do.

Post what you come up with and we'll work from there.

MIKE

http://www.skeptic.com/


Report •

#11
October 2, 2015 at 17:01:13
Mike,

re:

"The first test for Small is Less Than 56000
The second test is Greater Than 56000
Which, by process of elimination makes Equal to 56000, Medium."

Yes, the formula you posted in #2 will indeed return Medium for = 56000, but as we can see from #5, that is not what he wanted.

For all we knew (early on) mecerrato may have wanted to return Green or "" for =56000 since he didn't include that in his criteria.

Since mecerrato didn't specify what he wanted when E5 = 56000 all we could do is guess as to whether he wanted = 56000 to be Small or Medium or ... ?

Both you and AWTL assumed "Medium" when mecerrato was actually looking for "Small".

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


Report •

#12
October 2, 2015 at 18:30:53
DerbyDad03:

I made no assumptions,
I simply wrote the formula according to the stated criteria.
The formula is correct for the criteria stated.

Change the criteria, and you need to change the formula. :-)

MIKE

http://www.skeptic.com/


Report •

#13
October 2, 2015 at 19:54:47
I respectively disagree. The formula you offered returns "Medium" for =56000. The stated criteria was to return "Medium" for >56000 but <125000.

Since there was no request for a return value when E5 = 56000, I see 2 options:

1 - Assume -- would "guess" be a better word ;-) -- that "Medium" is the proper return value, or

2 - Ask the OP if E5 could ever =56000 and if so, what value would he like returned.

As we now know, he wants "Small" returned, so I think #2 would have been the correct option.

In addition, by asking the question, we help the OP by pointing out that the details matter and that in order to receive a solution that fits his requirements, we need to know *all* of the requirements. In other words, a teaching moment.

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


Report •

#14
October 3, 2015 at 06:13:40
mmcconaghy - I was able to figure out the vlookup in post $9 by reseraching it, thanks for the push to figure it out myself.
but...
can you help me get rid of the #VALUE! error when the cells are empty on this formula:

=IF(F15="Small",$Q$3*D18,IF(F15="Medium",$Q$4*D18,IF(F15="Large",$Q$5*D18)))

I tried prefixing it with =IFERROR but couldn't make it work. when the data is not there (because I don't have it yet, i would like for it to return 0 instead of the #VALUE!


Report •

#15
October 3, 2015 at 06:31:42
This is my formula:

=IFERROR(=IF(F15="Small",$Q$3*D18,IF(F15="Medium",$Q$4*D18,IF(F15="Large",$Q$5*D18))),0")

but Excel 2010 keeps telling that the formula contains an error, any help?


Report •

#16
October 3, 2015 at 07:52:42
When you acknowledge the dialog box for the error, does the cursor land in a particular location of your formula? It does for me. That should indicate where the error is.

Hint: "="

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


Report •

#17
October 3, 2015 at 10:33:38
There is a second error in the formula which is not related to the one hinted at by DerbyDad03.

Take a look at the third IF statement, the "Large", what is missing?

I was able to figure out the vlookup in post $9 by reseraching it,
We never spoke of a VLOOKUP.........

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#18
October 3, 2015 at 17:18:27
(Mike...shhhh! Don't tell him about that error. Let's just get him past the first error so he can get to the "suggestion" dialog box that Excel will offer. Let him take it from there.)

;-)

message edited by DerbyDad03


Report •

#19
October 5, 2015 at 06:27:15
Can someone help me figure this out?

D6 is a percentage that has to be capped at 200% for calculation purposes. I could write something to actually cap D6 at 200% in the cell itself but I actually need the real number for other purposes.
However, in the formula below I have to cap the calculation to 200%. Whether it is Small, Medium or Large the cap is 200% so the part of the formula that references the cell that has to be capped is D6. the value in D6 cannot be more than 200% in this formula, can anyone provide the solution?

=IFERROR(IF(F3="Small",$Q$3*D6,IF(F3="Medium",$Q$4*D6,IF(F3="Large",$Q$5*D6))),"")


Report •

#20
October 5, 2015 at 06:31:22
DerbyDad03 & mmcconaghy:

thanks for all your help but I don't see what is wrong with the last part ofr Large. The formula works and returns all correct values for SMall, Medium and Large:

=IFERROR(IF(F3="Small",$Q$3*D6,IF(F3="Medium",$Q$4*D6,IF(F3="Large",$Q$5*D6))),"")


Report •

#21
October 5, 2015 at 07:31:42
The formula you posted is this:

=IFERROR(IF(F3="Small",$Q$3*D6,IF(F3="Medium",$Q$4*D6,IF(F3="Large",$Q$5*D6))),"")

The problem with the third IF() is a bit difficult to see, and won't revel itself until you least expect it.

The syntax of an IF() statement is:

IF( logical_test , value_if_true , [value_if_false] )

Your third IF() statement is:

IF( F3="Large" , $Q$5*D6 )

Note that you DO NOT have a [value_if_false] section

It should be IF( F3="Large" , $Q$5*D6 , )

Note the addition of just the comma will correct the problem
and you will get a blank cell instead of one that returns the word FALSE.

MIKE

http://www.skeptic.com/


Report •

#22
October 5, 2015 at 07:39:40
mmcconaghy - makes sense, I made the correction, thanks for catching that :-)

Corrected formula:
=IFERROR(IF(F3="Small",$Q$3*D6,IF(F3="Medium",$Q$4*D6,IF(F3="Large",$Q$5*D6,""))),"")


Report •

#23
October 5, 2015 at 08:00:33
As for your second problem:

D6 is a percentage that has to be capped at 200% for calculation purposes.

Something could be written, but it would be very long and confusing, because, as you know with a formula, the cell your working from cannot change the contents of another cell.

So you can not change the value of D6 unless your in D6 so in your formula in every instance where you have D6 you would need to insert an additional IF() statement to check the value and then do the computation.

It would look something like:

=IFERROR(IF(F3="Small",$Q$3*IF(D6>200%,200%,D6),IF(F3="Medium",$Q$4*IF(D6>200%,200%,D6),IF(F3="Large",$Q$5*IF(D6>200%,200%,D6),))),"")


I could write something to actually cap D6 at 200% in the cell itself

That would probably be the easiest way to go.

As you can see, the layout of your sheet can have a lot to do with how much and what kind of processing you can do.

I don't know what your sheet looks like, but perhaps a redisign?

Also, in the future, different questions should be asked in their own threads.
Keeps things from getting confusing when answering and reading.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#24
October 5, 2015 at 16:42:44
Mike, the formula as you wrote it worked good, thanks

Report •

Ask Question