Can I specify a margin of difference when comparing cells?

[mission]

Hi all

Columns F and G contain numerical data and I've identified, with help here, where I've got +/- contravention. i.e., 1000 vs -1000 and vice versa.

Now, I want to be able to specify the margin of difference as a percentage of one amount. i.e., number in cell 2F is 50% diff to 2G etc.

Cheers

 

[Jonmo1]

Welcome to the board..


So if 2g is 100, then 2f can be within 50 of 2g (50-150)
If 2g is 200, then 2f can be within 100 of 2g (100-300)

Is that about right?

Try
=ABS(G2-F2)<=(G2*0.5)

 

[JackDanIce]

May be misinterpretting your request but try something like:

=ABS(F2)/ABS(F2-G2)

And then set the format of the cells to %

 

[mission]

thanks jonmo1

that works and have included it in IF function so returns "small" or "large" for the diff.

...however I'm getting a "large" diff here which is not right:

<TABLE style="WIDTH: 327pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=436 border=0 x:str><COLGROUP><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2713875 style="BORDER-RIGHT: #7a96df 0.5pt solid; BORDER-TOP: #7a96df 0.5pt solid; BORDER-LEFT: #7a96df 0.5pt solid; WIDTH: 122pt; BORDER-BOTTOM: #7a96df 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: orange" width=163 height=17>F</TD><TD class=xl24 style="BORDER-RIGHT: #7a96df 0.5pt solid; BORDER-TOP: #7a96df 0.5pt solid; BORDER-LEFT: #7a96df; WIDTH: 127pt; BORDER-BOTTOM: #7a96df 0.5pt solid; BACKGROUND-COLOR: orange" width=169>G</TD><TD class=xl23 style="BORDER-RIGHT: #7a96df 0.5pt solid; BORDER-TOP: #7a96df 0.5pt solid; BORDER-LEFT: #7a96df; WIDTH: 78pt; BORDER-BOTTOM: #7a96df 0.5pt solid; BACKGROUND-COLOR: orange" width=104>DIFF</TD></TR>

<TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl20 id=td_post_2713875 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="-3600">-3600.00 </TD><TD class=xl20 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-3520.0338939999929">-3520.03 </TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">large</TD></TR></TBODY></TABLE>

 

[Jonmo1]

OK, didn't realize you were dealing with negative numbers..

try

=IF(ABS(G2-F2)<=(ABS(G2)*0.5),"Small","Large")

 

[mission]

that's got it. cheers.