Prev: Formula to pull the lowest supplier name
Next: Exported data from Banner database to Excel
From: Vicki on 4 Jun 2010 13:05
Please help!

I am trying to get the formula below to return a value to the cell ONLY if
the answer to ((c30-$30)/g$30) is greater or less than 20%.

I am using the below formula.

=IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)>ABS(20),((C30-$G30)/$G30)," "))

Thanks

From: Bob Phillips on 4 Jun 2010 14:55
Try

=IF(ISERROR((C30-$G30)/$G30),"-",IF(ABS(C30-$G30)/$G30>0.2,(C30-$G30)/$G30,"
"))

--

HTH

Bob

"Vicki" <Vicki(a)discussions.microsoft.com> wrote in message
news:22EA6740-B9B9-4834-8AEA-217DD36C3B4D(a)microsoft.com...
> Please help!
>
> I am trying to get the formula below to return a value to the cell ONLY if
> the answer to ((c30-$30)/g$30) is greater or less than 20%.
>
> I am using the below formula.
>
> =IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)>ABS(20),((C30-$G30)/$G30),"
> "))
>
> Thanks
>


From: "David Biddulph" groups [at] on 4 Jun 2010 15:06

=IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)<>20%,((C30-$G30)/$G30),"
"))

Your formula had greater than, but not less than.
20 is different from 20% (by a factor of 100).
You don't need the ABS function.
--
David Biddulph


"Vicki" <Vicki(a)discussions.microsoft.com> wrote in message
news:22EA6740-B9B9-4834-8AEA-217DD36C3B4D(a)microsoft.com...
> Please help!
>
> I am trying to get the formula below to return a value to the cell ONLY if
> the answer to ((c30-$30)/g$30) is greater or less than 20%.
>
> I am using the below formula.
>
> =IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)>ABS(20),((C30-$G30)/$G30),"
> "))
>
> Thanks
>

From: Steve on 4 Jun 2010 15:34
What numbers are likely to be in cells C30 and G30
What should it be 20% of
Why use ABS
What errors do you anticipate

Regards
Steve

"Vicki" wrote:

> Please help!
>
> I am trying to get the formula below to return a value to the cell ONLY if
> the answer to ((c30-$30)/g$30) is greater or less than 20%.
>
> I am using the below formula.
>
> =IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)>ABS(20),((C30-$G30)/$G30)," "))
>
> Thanks
>
From: Vicki on 4 Jun 2010 16:34
Bob,

Thank you so much for your input. This formula worked GREAT!

"Bob Phillips" wrote:

> Try
>
> =IF(ISERROR((C30-$G30)/$G30),"-",IF(ABS(C30-$G30)/$G30>0.2,(C30-$G30)/$G30,"
> "))
>
> --
>
> HTH
>
> Bob
>
> "Vicki" <Vicki(a)discussions.microsoft.com> wrote in message
> news:22EA6740-B9B9-4834-8AEA-217DD36C3B4D(a)microsoft.com...
> > Please help!
> >
> > I am trying to get the formula below to return a value to the cell ONLY if
> > the answer to ((c30-$30)/g$30) is greater or less than 20%.
> >
> > I am using the below formula.
> >
> > =IF(ISERROR((C30-$G30)/$G30),"-",IF(((C30-$G30)/$G30)>ABS(20),((C30-$G30)/$G30),"
> > "))
> >
> > Thanks
> >
>
>
> .
>
 | 
Pages: 1
Prev: Formula to pull the lowest supplier name
Next: Exported data from Banner database to Excel