Trouble with < and > formula, dividing into quarters

[cjvhowells]

Hi there

I'm an absolute novice when it comes to Excel, and got so far with a lot of trial and error (and youtube videos). Unfortunately I've hit a wall!

I have an item at 4000mm in length, and I'm trying to make a cell that will divide it into quarters depending on the number entered into another cell.

i.e., if 0-1000 is entered into cell A then divide cell B by 0.25, if 1001-2000 is entered into cell A then divide cell B by 0.5 etc.

I've entered the formula below, it's allowing me to enter it without any errors but entering anything over 2000 doesn't seem to have any affect:

=IF(C4<1000,G10*0.25,IF(C4>OR1001,G10*0.5,IF(C4>OR52001G10*0.75,IF(C4>OR4001,G10*1))))

I'm guessing this is something to do with trying to use more than two lesser then/greater than sums in one formula?

 

[etaf]

=IF(C4<1000,G10*0.25,IF(C4>OR1001,G10*0.5,IF(C4>OR52001G10*0.75,IF(C4>OR4001,G10*1))))

what are all the ranges

maybe start at the largest and work down

=IF ( C4 > 4000 , G10* 1 , IF( C4 > 2000, G10 * .75 , If( C4 > 1000 , G10*0.5 , G10*0.25 ) ) )

 

[cjvhowells]

Thanks for the quick response! That seems to be working now, but If( C4 > 1000 , G10*0.25 ) ) ) ) is now giving a result of FALSE

=IF(C4>4000,G10*1,IF(C4>3000,G10*0.75,IF(C4>2000,G10*0.5,IF(C4>1000,G10*0.25))))

 

[etaf]

you dont have a false in the nested if

so your criteria does not include 0-1000

if you look at mine - i have a false after the >1000

remember the IF works left to right
AND also uses
IF ( Test, True, False)

so when nesting

IF ( Test, True, IF ( Test, True, False))

IF ( Test, True, IF ( Test, True, IF ( Test, True, False)))

=IF(C4>4000,G10*1,IF(C4>3000,G10*0.75,IF(C4>2000,G10*0.5,IF(C4>1000,G10*0.25, NEEDS A FALSE here for less than 1000 ))))

otherwise the formula just returns FALSE

can put a "" so leave blank or 0 to put a zero if less than 1000

 

[Fluff]

Maybe

=IF(C4<1000,G10*0.25,IF(C4<2000,G10*0.5,IF(C4<3000,G10*0.75,G10)))

 

[Fluff]

Correction to my previous formula

=IF(C4<=1000,G10*0.25,IF(C4<=2000,G10*0.5,IF(C4<=3000,G10*0.75,G10)))