Compare allowing variance
 

Hi,

I've been goind around with this and am not sure how to accomplish it. I am
trying to compare two values, but I want to allow a variance of plus or minus
the value in Gross Up I2. The formula used depends on whether G7 begins with
"Pro". What I have only works for a positive number, not a negative. How
would I do this?

=IF(LEFT($G$7,3)="Pro",IF(SUM($G$11-SUM('GL Line Items'!$E$10/'Gross Up'! _
F2*$B$3))'Gross Up'!I2,"Totals do not match",""),IF(SUM($G$11-'GL Line
Items'! _
$E$10)'Gross Up'!I2,"Totals do not match",""))
--
Thanks for your help.
Karen53

Compare allowing variance
 

Hi,
Your formulation would be equivalent to:
Abs( GrossUp - Value) < Variance
i.e.

=IF(LEFT($G$7,3)="Pro"
,IF(ABS( <Total1 - <Value1 ) < <Variance1 , "", "No Match")
,IF(ABS( <Total2 - <Value2 ) < <Variance2 , "", "No Match) )

Just replace <Total1, <Total2, <Value1, <Value2, <Varaince1,
<Variance2 with the cell references.
Note: no need of SUM(A1-A2), you can just write A1-A2

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Karen53" wrote:

Hi,

I've been goind around with this and am not sure how to accomplish it. I am
trying to compare two values, but I want to allow a variance of plus or minus
the value in Gross Up I2. The formula used depends on whether G7 begins with
"Pro". What I have only works for a positive number, not a negative. How
would I do this?

=IF(LEFT($G$7,3)="Pro",IF(SUM($G$11-SUM('GL Line Items'!$E$10/'Gross Up'! _
F2*$B$3))'Gross Up'!I2,"Totals do not match",""),IF(SUM($G$11-'GL Line
Items'! _
$E$10)'Gross Up'!I2,"Totals do not match",""))
--
Thanks for your help.
Karen53

Compare allowing variance
 

Thank you, Sebastien!

--
Thanks for your help.
Karen53


"sebastienm" wrote:

Hi,
Your formulation would be equivalent to:
Abs( GrossUp - Value) < Variance
i.e.

=IF(LEFT($G$7,3)="Pro"
,IF(ABS( <Total1 - <Value1 ) < <Variance1 , "", "No Match")
,IF(ABS( <Total2 - <Value2 ) < <Variance2 , "", "No Match) )

Just replace <Total1, <Total2, <Value1, <Value2, <Varaince1,
<Variance2 with the cell references.
Note: no need of SUM(A1-A2), you can just write A1-A2

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Karen53" wrote:

Hi,

I've been goind around with this and am not sure how to accomplish it. I am
trying to compare two values, but I want to allow a variance of plus or minus
the value in Gross Up I2. The formula used depends on whether G7 begins with
"Pro". What I have only works for a positive number, not a negative. How
would I do this?

=IF(LEFT($G$7,3)="Pro",IF(SUM($G$11-SUM('GL Line Items'!$E$10/'Gross Up'! _
F2*$B$3))'Gross Up'!I2,"Totals do not match",""),IF(SUM($G$11-'GL Line
Items'! _
$E$10)'Gross Up'!I2,"Totals do not match",""))
--
Thanks for your help.
Karen53