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

[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

 

[Ken Wright]

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
----------------------------------------------------------------------------

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.

 

[Michael Puk]

=B1*(IF(A1/500>INT(A1/500),INT(A1/500)+1,IF(A1/500=INT(A1/500),INT(A1/500),1
)))

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.