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"

Something like this may work =IF(E5<56000,"SMALL",IF(AND(E5>=56000,E5<125000),"Medium",IF(E5>=125000,"Large")))

message edited by AlwaysWillingToLearn

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 forLarge

What's left has to beMediumMIKE

But wait... The OP didn't tell us what he wants returned if E5

56000.equals

message edited by DerbyDad03

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"

I did post the formula.

This one is modified with your new criteria:=IF(E5<=56000,"Small",IF(E5>=125000,"Large","Medium"))

DerbyDad03In the OP's

originalrequest 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

mmcconaghy - it returns LARGE for all values

Works fine on my machine. Make sure that cell E5 is formatted as NUMBER,

notTEXT

If you enter a Number and it stays Aligned to the Left inside the Cell, then it's TEXT.

MIKE

message edited by mmcconaghy

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 :-)

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%

Can you help me write this formulaTry writing it yourself, best way to learn is to do.

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

MIKE

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

Greenor "" 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.

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

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.

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!

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?

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.

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

message edited by mmcconaghy

(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

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))),"")

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))),"")

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

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,""))),"")

As for your second problem:

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

couldbe written, but it would be very long and confusing, because, as you know with a formula, the cell your working from cannot change thecontentsof 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 itselfThat 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

message edited by mmcconaghy

Mike, the formula as you wrote it worked good, thanks

Ask Your Question

Weekly Poll