I am looking for a formula in excel to do the following: If B13 is less than .095 then display "R"

If B13 is between .095-0.98 then display "Y"

If B13 is greater than .098 then display "G"

✔ Best Answer

First, I'll assume (dangerous) that when you say "between 0.95-0.98" you mean >=.95 and <=.98. Technically "between" does not include .95 and .98 but your other 2 criteria seem to indicate that you want to include those values for Y.

Second, I'll assume (dangerous) that your values will either only include 2 digits or that your cell will display all digits if there are more. The reason I bring this up is that the formula is going to use the value that is

inthe cell which may not be what isdisplayed. Allow me to explain:If the cell contains .946, the formula is going to return R because that value is <.95. However, if the cell is formatted to only show 2 digits, it will

display.95 and the user will wonder why the result isn't Y. If that is going to be an issue, we can introduce some rounding functions so that .946 will actually become .95 and there will be no confusion.That said, try the following formula. Since an IF function stops checking as soon as a TRUE clause is found, we can start high and work our way down.

IF B13 is greater than .98, return G and stop.

IF B13 is not greater than .98, then check to see if it's greater than >=.95. If it is, return Y and stop.

If B13 is neither >.98 nor >= .95, then it must be less than .95, so return R. The formula doesn't have to check for less than .95 because the valuemustbe less than .95 if it isn't >= .95.

=IF(B13>0.98,"G",IF(B13>=0.95,"Y","R"))We can also start low work our way higher:

=IF(B13<0.95,"R",IF(B13<=0.98,"Y","G"))

message edited by DerbyDad03

Thanks for letting us know what you are looking for. If you think that we can be of assistance, feel free to ask for help.

message edited by DerbyDad03

I would like a formula provided for the scenario, yes :)

Well, that still wasn't an actual requestas in "Can someone please help me by providing a formula? Thanks!" but we'll let you slide this one time.That said, your original question doesn't seem to make sense. You have asked for 2 conflicting results.

You want G if the value is greater than .098, but you also what Y up to .98.

For example, .75 is "between .095-0.98" but also "greater than .098".

Should that answer be G or Y?

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

I'm sorry :( the 0 is completely irrelevant and I put the period in the wrong place :p R = Greater than .95

Y = .95 to .98

G = Above .98Thanks for the help :)

Try again. In your OP you said (more or less) "R = Less than .95", now you say "R = Greater than .95".

Which is it?

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

Sadly, it's what happens when a nagging co-worker is bugging me. If B13 is less than 0.95 then display "R"

If B13 is between 0.95-0.98 then display "Y"

If B13 is greater than 0.98 then display "G"

First, I'll assume (dangerous) that when you say "between 0.95-0.98" you mean >=.95 and <=.98. Technically "between" does not include .95 and .98 but your other 2 criteria seem to indicate that you want to include those values for Y.

Second, I'll assume (dangerous) that your values will either only include 2 digits or that your cell will display all digits if there are more. The reason I bring this up is that the formula is going to use the value that is

inthe cell which may not be what isdisplayed. Allow me to explain:If the cell contains .946, the formula is going to return R because that value is <.95. However, if the cell is formatted to only show 2 digits, it will

display.95 and the user will wonder why the result isn't Y. If that is going to be an issue, we can introduce some rounding functions so that .946 will actually become .95 and there will be no confusion.That said, try the following formula. Since an IF function stops checking as soon as a TRUE clause is found, we can start high and work our way down.

IF B13 is greater than .98, return G and stop.

IF B13 is not greater than .98, then check to see if it's greater than >=.95. If it is, return Y and stop.

If B13 is neither >.98 nor >= .95, then it must be less than .95, so return R. The formula doesn't have to check for less than .95 because the valuemustbe less than .95 if it isn't >= .95.

=IF(B13>0.98,"G",IF(B13>=0.95,"Y","R"))We can also start low work our way higher:

=IF(B13<0.95,"R",IF(B13<=0.98,"Y","G"))

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History