Need Help With Formula

[storeman]

I would like to create a formula that will do the following:
check to see if there is a particular value in a cell and return
different value if the reference cell matches the value or 0. If no
then return "Input Error". I can get a formula to do either the "Inpu
Error" or the value but I do not know how to write one that will d
either.

I am not very proficient at writing Excel formulii but here is what
have tried so far.
=IF(A1=100,10,"") This returns the value 10 or nothing if A1=100 or 0
This is fine but I also want the formula to return "Input Error" i
something other than 100 or 0 is entered in A1.

Th

 

[Alan]

=IF(A1=0,"",IF(A1=100,10,"Input Error"))
Regards,

 

[Ron Rosenfeld]

I would like to create a formula that will do the following:
check to see if there is a particular value in a cell and return a
different value if the reference cell matches the value or 0. If not
then return "Input Error". I can get a formula to do either the "Input
Error" or the value but I do not know how to write one that will do
either.

I am not very proficient at writing Excel formulii but here is what I
have tried so far.
=IF(A1=100,10,"") This returns the value 10 or nothing if A1=100 or 0.
This is fine but I also want the formula to return "Input Error" if
something other than 100 or 0 is entered in A1.

=IF(A1=100,10,IF(A1=0,"","Input Error"))

However, if A1 is blank, this formula will also return a null string. Is that
what you want?

Another method of ensuring proper input is to use Data Validation on cell A1.
Something like Data/Validation Custom Formula: =OR(A1=0,A1=10) and then your
original formula would work as is.


--ron

 

[Dingo]

Try this :

=IF(OR(A1=100;A1=0);10;"Input error")

Cell A1 is your input figure
If you want to change the figures 100 and/or 0 then compare A1 to a
reference cell where you key in the other figures.

 

[Rescueme]

Thanks for the help - I figured out my problem, the cells in my talbe it was
looking them up in were not formated correctly - some had a space in front
of the number which through everything off. Formula from Frank is working
great.