Solved Excel formula to show text if certain percentages are true

June 27, 2018 at 14:16:21
Specs: Windows 10
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"


See More: Excel formula to show text if certain percentages are true

Report •

✔ Best Answer
June 27, 2018 at 20:13:41
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 in the cell which may not be what is displayed. 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 value must be 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



#1
June 27, 2018 at 14:35:47
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


Report •

#2
June 27, 2018 at 14:56:20
I would like a formula provided for the scenario, yes :)

Report •

#3
June 27, 2018 at 15:10:28
Well, that still wasn't an actual request as 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


Report •

Related Solutions

#4
June 27, 2018 at 15:15:15
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 .98

Thanks for the help :)


Report •

#5
June 27, 2018 at 15:24:35
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


Report •

#6
June 27, 2018 at 19:24:37
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"


Report •

#7
June 27, 2018 at 20:13:41
✔ 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 in the cell which may not be what is displayed. 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 value must be 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


Report •

Ask Question