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

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

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