=IF(H144=FALSE,MIN(1,ABS(1-F144/D144)),IF(C144=0,"no value",H144))

--
HTH

Bob Phillips

"Jean" wrote in message
...
When I add the MIN(1,...) to my calculation it doens't return a value.
Here
is the formula I am using and I wish to keep the value returned at less
than
100%.

=IF(H144=FALSE,ABS(1-F144/D144),IF(C144=0,"no value",H144)) this formula
works correctly for everything I need except if F144 is greater than D144.


"Bob Phillips" wrote:

Simply stated, you need to force a maximum value of 1, so you take the
minimum of 1 (100%) or your calculation

=IF(H5=??,MIN(1,ABS(1-F5/d5)))

--
HTH

Bob Phillips

"Jean" wrote in message
...
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" wrote in message
...
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