Articles

Solved Excel - Greater Than and Less Than

August 28, 2010 at 16:05:23
Specs: Windows XP

Hello,

I'm trying to create a formula with the following rules:

1) If greater than 10% or 0.1, give a value of 2
2) If greater than 5% or 0.05 and less than 10% or 0.1, give a value of 1
3) If less than 5% or 0.05, give a value of 0

I've figured out how to do each one individually, but can't get them to work together in the same statement.

Here's what I have so far:

1) =IF(H3>0.1,2,FALSE)
2) =IF(AND(H3<0.1, H3>0.05),1,FALSE)
3) =IF(H3<0.05,0,FALSE)

Any help would be greatly appreciated!

Sincerely,


-Ted


See More: Excel - Greater Than and Less Than

Report •


#1
August 28, 2010 at 17:00:24
✔ Best Answer

Try this:

=IF(H3<0.05,0,IF(H3>0.1,2,1))

MIKE

http://www.skeptic.com/


Report •

#2
August 28, 2010 at 17:14:05

Mike,

Thanks. It worked like a charm!

Much appreciated.

Sincerely,


-Ted


Report •

#3
August 28, 2010 at 17:35:31

Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
August 28, 2010 at 17:58:20

But Mike,

You forgot to ask a very important question:

What if the value equals .05 or .1?

(or is that not a possibility?)


Report •

#5
August 28, 2010 at 18:19:58

Hi DerbyDad03,

The equaling of .05 or .1 would be possible in extreme cases. How would I adjust Mike's formula to account for these scenarios?

Also, thanks for bringing it to my attention.

Sincerely,


-Ted


Report •

#6
August 28, 2010 at 19:19:58

Well, in order to accurately suggest an "adjustment" we would need to know what you want the result to be if .05 or .1 were to occur.

This will work if you want the same values you asked for earlier (0 and 2) for "less than or equal to .05" and "greater than or equal to 0.1."

=IF(H3<=0.05,0,IF(H3>=0.1,2,1))

If you want something different, let us know.


Report •

#7
August 29, 2010 at 12:39:18

DerbyDad03,

Thanks. I updated the formula to...

=IF(H3<0.05,0,IF(H3>=0.1,2,1))

which suits my needs.

Sincerely,


-Ted


Report •

#8
August 29, 2010 at 15:20:37

As long as you realize that = .05 is going to return 1, then I guess you are good to go.

Report •

#9
September 1, 2010 at 19:17:56

DerbyDad03,

Thanks, that's what I wanted.

Sincerely,


-Ted


Report •

#10
January 21, 2011 at 18:51:48

Hi,

I need a formula that would allow me to use the value of the cell if its LESS than the maximum limit or cap and for me to use the maximum number if the value is GREATER than the max limit. To make things more complicated there is a list of maximum value and not just one. Here is what i have so far which gives me just the maximum number :
=IF(ISERROR(IF(OR(M36<=(VLOOKUP($D36,MBU!$A:$B,2,FALSE))),'MBU Raw'!M36,"")),M36,IF(OR(M36>(VLOOKUP($D36,MBU!$A:$B,2,FALSE))),SUMIF(MBU!A:A,'MBU Raw'!D36,MBU!C:C),""))

here is another formula i used but then its not using the maximum value when the value is greater than the max:

=IF(ISERROR(IF(OR(M36<=(VLOOKUP($D36,MBU!$A:$B,2,FALSE))),'MBU Raw'!M36,"")),0,IF(OR(M36>(VLOOKUP($D36,MBU!$A:$B,2,FALSE))),M36,""))

please help!


QNY


Report •

#11
January 22, 2011 at 07:08:04

QNY, please re-post your question in a new thread with examples of your data and read this:
http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •


Ask Question