Help with Excel Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
B said:
Hi, I need help with an Excel formula. This is probably very easy,
Click to expand...
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
Click to expand...
results, or error messages about too many argument, etc. If anyone
can help I'd really appreciate it. Thank you in advance.
 
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
-----------------------------------------------------
B said:
Hi, I need help with an Excel formula. This is probably very easy, but I'
Click to expand...
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
Click to expand...
results, or error messages about too many argument, etc. If anyone can help
I'd really appreciate it. Thank you in advance.
 
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 ]
 
Hi, I just wanted to say thanks so much to all who responded. I plugged in your suggestions and... Success! :

Thanks again

-B
 
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
Click to expand...

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.
B said:
Hi, I just wanted to say thanks so much to all who responded. I
Click to expand...
plugged in your suggestions and... Success! :)
 
Back
Top