Formula: If less than X but greater than Y...

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

Guest

Trying to create a single formula that does this:

If A1 is less than 500, show B1, if A1 is greater than 500 but less than 1000, show B1*2, if A1 is greater than 1000 but less than 1500, show B1*3, if A1 is greater than 1500 but less than 2000, show B1*3... and so on.

Help!

PS: Using Excel 2000
 
With your number in A1:-

=B1*(INT(A1/500)+1)

Watch out for what happens when it is actually 500, 1000, 1500 etc because you didn't say.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Aman said:
Trying to create a single formula that does this:

If A1 is less than 500, show B1, if A1 is greater than 500 but less than 1000, show
Click to expand...
B1*2, if A1 is greater than 1000 but less than 1500, show B1*3, if A1 is greater than 1500
but less than 2000, show B1*3... and so on.
 
=B1*(IF(A1/500>INT(A1/500),INT(A1/500)+1,IF(A1/500=INT(A1/500),INT(A1/500),1
)))


Aman said:
Trying to create a single formula that does this:

If A1 is less than 500, show B1, if A1 is greater than 500 but less than
Click to expand...
1000, show B1*2, if A1 is greater than 1000 but less than 1500, show B1*3,
if A1 is greater than 1500 but less than 2000, show B1*3... and so on.
 
Back
Top