Help with Excel Formula

[B]

Hi, I need help with an Excel formula. This is probably very easy, but I’m just not good with these things. All I want to do is the following: if the value in cell E2 is less than or equal to 1000, I want the value in cell H2 to be 1. If the value in cell E2 is 0, then I want the value in cell H2 to be 0. If the value in cell E2 is greater than 1000, then I want the value in cell H2 to be E2/1000. I hope I’ve explained this clearly. I started out using this sort of formula that I thought I could build on:

=IF(AND(1<E2, E2<=1000),1,0)

However, whenever I try to add more I keep getting either incorrect results, or error messages about too many argument, etc. If anyone can help I’d really appreciate it. Thank you in advance.

-B

 

[Frank Kabel]

Hi
try the following in H2:
=IF(E2=0,0,IF(E2<=1000,1,E2/1000))

 

[Norman Harker]

Hi B!

Try the following in H2:
=IF(E2<=0,0,IF(E2<=1000,1,E2/1000))

I've added that H2 should be 0 if E2<0

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi, I need help with an Excel formula. This is probably very easy,

but I'm just not good with these things. All I want to do is the
following: if the value in cell E2 is less than or equal to 1000, I
want the value in cell H2 to be 1. If the value in cell E2 is 0, then
I want the value in cell H2 to be 0. If the value in cell E2 is
greater than 1000, then I want the value in cell H2 to be E2/1000. I
hope I've explained this clearly. I started out using this sort of
formula that I thought I could build on:

=IF(AND(1<E2, E2<=1000),1,0)

However, whenever I try to add more I keep getting either incorrect

results, or error messages about too many argument, etc. If anyone
can help I'd really appreciate it. Thank you in advance.

 

[Max]

Put in H2: =IF(E2=0,0,IF(E2<=1000,1,IF(E2>1000,E2/1000,"")))

[above will return "blank" if E2<0 -- this spec is assumed, as you did not
mention in your post]

--
Rgds
Max
xl 97
----------------------------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------------------

Hi, I need help with an Excel formula. This is probably very easy, but I'

m just not good with these things. All I want to do is the following: if
the value in cell E2 is less than or equal to 1000, I want the value in cell
H2 to be 1. If the value in cell E2 is 0, then I want the value in cell H2
to be 0. If the value in cell E2 is greater than 1000, then I want the
value in cell H2 to be E2/1000. I hope I've explained this clearly. I
started out using this sort of formula that I thought I could build on:

=IF(AND(1<E2, E2<=1000),1,0)

However, whenever I try to add more I keep getting either incorrect

results, or error messages about too many argument, etc. If anyone can help
I'd really appreciate it. Thank you in advance.

 

[Max]

Sorry, scratch earlier suggestion.

Try instead in H2:

=IF(E2<=0,0,IF(E2<=1000,1,E2/1000))

[ returns 0 if E2<0 -- spec is assumed ]

 

[B]

Hi, I just wanted to say thanks so much to all who responded. I plugged in your suggestions and... Success! :

Thanks again

-B

 

[Norman Harker]

Hi B!

Now just take a look at the logic and structure of the solutions.

We all worked from left to right on the number line from negative to

1000

The true condition E2<=0 covers all those cases leaving two
alternative for the remaining ones (E2<=1000 and E2>1000)

That's covered by nesting an IF function in the false value of the
first IF function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi, I just wanted to say thanks so much to all who responded. I

plugged in your suggestions and... Success!