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 0I'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

Mike, Thanks. It worked like a charm!

Much appreciated.

Sincerely,

-Ted

But Mike, You forgot to ask a very important question:

What if the value

equals.05 or .1?(or is that not a possibility?)

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

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.

DerbyDad03, Thanks. I updated the formula to...

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

which suits my needs.

Sincerely,

-Ted

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

DerbyDad03, Thanks, that's what I wanted.

Sincerely,

-Ted

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

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

Ask Your Question

Weekly Poll