Ignoring two figures if their rounded value is the same

[Mr2017]

Hi

In the table below, cells B1 and C1 are 0.2178 AND 0.2199, respectively.

The other cells are
A2=A
A3=B
B2=22%
C2=22%
B3=20%
C3=20%

[TABLE="width: 256"]
<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]0.2178
[/TD]
[TD="width: 64, align: right"]0.2199[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl65, align: right"]22%
[/TD]
[TD="class: xl65, align: right"]22%
[/TD]
[TD] A
[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="class: xl65, align: right"]20%
[/TD]
[TD="class: xl65, align: right"]25%[/TD]
[TD] B
[/TD]
[/TR]
</tbody>[/TABLE]


I want to create a formula in column D that looks at the values in column C and B and does this:

i) If the values, when rounded up, in columns B and C are the same, then return a blank in column D (so the letter "A" should NOT be present in column D.
ii) if the value in column C is greater than the value in column B, then return the value in column A -ie the value "B" should be present in column D because 25% is > 20%.

This formula worked for the row with the two 22% values, but it didn't work for the row with 20% and 25%.

=IF(ROUNDUP(B2,0)=ROUNDUP(C2,0),"",IF(C2>B2,A2,""))

Any thoughts would be helpful!

Thanks in advance!

[TABLE="width: 256"]
<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

25% is 0.25. So, try this:

=IF(ROUNDUP(B2,2)=ROUNDUP(C2,2),"",IF(C2>B2,A2,""))

 

[FDibbins]

Keep in mind that, even though the cell may display 25%, the underlying content will still be a decimal 0.25something

 

[Mr2017]

Awesome!

That worked!!

Thanks Tetra!

 

[Mr2017]

Hi Tetra

I'd like to clarify this, if I may, please.

Table 1 below is in columns G to K, from cell G1 to K9. Column I is blank.

The percentages are rounded up, and there is only one row where two percentages are the same ie 22%.


The column headers are "Letter" in G1, "Total" in H1, blank in I1, "TY" in K1 and "Over in K1".

In the second table below, you can see the actual decimal values. The result I'm trying to achieve is that if two rounded up figures are the same ie the 22% values, then nothing is returned in column K. But if the value in column K is greater than the value in column G, then the letter in column G is returned - if this condition is not satisfied, then a blank should be returned. Is it possible to do that?

At present, the formula returns a blank in the row where the 10% and 9% values exist, but it should return the letter D, because the 10% is greater than 9%.

Likewise, it's also returning blanks in the row with 4% and 5%. Let me know if you want me to clarify anything.

Table 1

[TABLE="width: 384"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Letter[/TD]
[TD]Total[/TD]
[TD] [/TD]
[TD]TY[/TD]
[TD]Over [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10%[/TD]
[TD] [/TD]
[TD="align: right"]7%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]12%[/TD]
[TD] B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]22%[/TD]
[TD] [/TD]
[TD="align: right"]22%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]9%[/TD]
[TD] [/TD]
[TD="align: right"]10%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]15%[/TD]
[TD] [/TD]
[TD="align: right"]12%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]4%[/TD]
[TD] [/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]9%[/TD]
[TD] [/TD]
[TD="align: right"]10%[/TD]
[TD] G[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 330"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Letter [/TD]
[TD]Total[/TD]
[TD] [/TD]
[TD]TY[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10.31%[/TD]
[TD] [/TD]
[TD="align: right"]7.32%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4.74%[/TD]
[TD] [/TD]
[TD="align: right"]12.20%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]21.73%[/TD]
[TD] [/TD]
[TD="align: right"]21.95%[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]9.19%[/TD]
[TD] [/TD]
[TD="align: right"]9.76%[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]15.04%[/TD]
[TD] [/TD]
[TD="align: right"]12.20%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]4.18%[/TD]
[TD] [/TD]
[TD="align: right"]4.88%[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]8.64%[/TD]
[TD] [/TD]
[TD="align: right"]9.76%[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD="align: right"]100%
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!

 

[friel300]

its because you are using round up in the formula but the rounding in the cell formatting (TOP TABLE) will go to the closest decimal.

if you use this instead i think it will work for you

=IF(ROUND(H2,2)=ROUND(J2,2),"",IF(J2>H2,G2,""))

 

[Mr2017]

Amazing! Thanks friel300!

I didn't get a notification email to say that someone had responded, hence the late response.

But your suggestion worked! Thank you very much!

I hope you have a great day!