Ok, there is one final problem that I need to get resolved. When calculating
the percentage accuarcy how do you keep the absolute vallue at no greater
than 100%.
Example: my current calculation, is created when there are numbers so from
below, the "??". the formula currently reads IF(H5=??,ABS(1-F5/d5).
Where/how do I amend the formula so that it iehter take the ABS(1-F5/d5) or
if the result is greater than 100%, then it returns only 100%.

Jean

"Bob Phillips" wrote:

> =IF(OR(A1=0,B1=0),0,IF(AND(A1=0,B1=0),1,"??"))
>
> and format as a percentage.
>
> You don't say what to do if neither are 0, so I just added "??"
>
> --
> HTH
>
> Bob Phillips
>
> "Jean" <Jean@discussions.microsoft.com> wrote in message
> news:7283C66F-C495-4008-9E1C-F09C29CF500C@microsoft.com...
> > bj:
> > I need help with a logical staement and you seem to be an expert. I am
> > trying to calculate a forecast accuracy. I need to do more than one piece
> of
> > logic with in the formula.
> > If we focus on 2 columns, i have column A that is a forecast number and
> > column b that is an actual sales number. I have covered the case of if
> > column A or B is equal to zero then enter a zero % accuarcy, but I also
> need
> > to cover if both column A and B are equal to zero then 100% accuracy. Can
> > you help??
> >
> > "bj" wrote:
> >
> > > try
> > >
> =if(sumproduct(--(A1:A1000=name),--(B1:B1000="X")--(C1:C1000="X")--(D1:D1000
> ="X")--(E1:E1000))>0, "ok","nope")
> > >
> > > "Scott" wrote:
> > >
> > > > Hey bj,
> > > >
> > > > What I actually need to do is check for both the name and an X in a
> > > > particular column. The worksheet I am working with has a list of
> names, and
> > > > four columns that indicate which week a person is attending an event.
> I have
> > > > a summary sheet I am preparing that I want to look-up instances of the
> > > > person's name and which week they are involved. So I have to check
> for both
> > > > cases, appearance of the name and an x in the week one column to
> return an
> > > > "OK" in the summary sheet.
> > > >
> > > > I know this is confusing. If I can email you an example let me know.
> > > >
> > > > Thanks in advance.
> > > >
> > > > Scott
> > > >
> > > >
> > > > "bj" wrote:
> > > >
> > > > > try If you want to enter the name in Cell C1 and have D1 give the
> result
> > > > > in D1 enter a
> > > > > =if(Vlookup(C1,$A$1:$B$99,2,false)="X","OK","nope"
> > > > > and in C1 enter the name
> > > > > "Scott" wrote:
> > > > >
> > > > > > This might be hard to explain...
> > > > > >
> > > > > > I want to check a sheet for a persons name AND if an X is entered
> in an
> > > > > > associated cell. In literal terms the IF statement would be as
> follows:
> > > > > >
> > > > > > IF ("John Doe" is found in range A1:A99 AND there is an X in field
> B1, THEN
> > > > > > "OK")
> > > > > >
> > > > > > The X would appear in the same row as the name in the range, for
> example:
> > > > > > Field A1 contains "John Doe" and field B1 contains "X".
> > > > > >
> > > > > > How would I make this work?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Scott
>
>
>