# Calculate if< and or > than



## Bedford (Jan 1, 2023)

Struggling with a formula to provide a result of either "1" or "2", depending of the contents of cell D17. I started with this; =IF(D17<2500,=1(D17>2500,(D17/2,1""))), but it is definitely wrong. 
If D17 is < 2500 it should result with "1", if > 2500 the result should be "2".
Please help!


----------



## etaf (Jan 1, 2023)

what if it equals 2500

This will provide a 2 if = 2500

=IF( D17 < 2500 , 1 , 2 )

If its not less , then it will be more
Or it could be empty

what happens then

=If( D17 = "", "", IF ( D17 < 2500 , 1 , 2 ) )

"1" is text , so if you need to use as a number leave off the ""

This will provide a 1 if = 2500
=If( D17 = "", "", IF ( D17 < = 2500 , 1 , 2 ) )


----------



## Bedford (Jan 1, 2023)

etaf said:


> what if it equals 2500
> 
> =IF( D17 < 2500 , 1 , 2 )
> 
> ...


Ideally if the number is < 2500 it should be anything 2499 and below, then the result would be 1, if the number is 2500 or > 2500, it should result with 2 to a maximum of 4999, and should D17 be 5000 or > 5000 it should result with 3 to a maximum of 7499, if it increases to 7500 or > 7500 then result would be 4 to a maximum of 9000.
Kind of complicated now.


----------



## etaf (Jan 1, 2023)

what happens at 9000
is 4 the max ?

=IF( D17 < 2500 , 1 , IF( D17 < 5000 , 2 , IF( D17 < 7500, 3 , IF( D17 < 9000 , 4 , "over 9000" ) )))

you may need a lookup table
Or other function

But thats a simple Nested IF

we could use IFS()

but lets see your answer on 9000


----------



## Bedford (Jan 1, 2023)

etaf said:


> what happens at 9000
> is 4 the max ?
> 
> =IF( D17 < 2500 , 1 , IF( D17 < 5000 , 2 , IF( D17 < 7500, 3 , IF( D17 < 9000 , 4 , "over 9000" ) )))
> ...


Definitely the max is 9000, so if 9001 is entered, it should error.


----------



## etaf (Jan 1, 2023)

so 9000 = 4

=IF( D17 < 2500 , 1 , IF( D17 < 5000 , 2 , IF( D17 < 7500, 3 , IF( D17 <  = 9000 , 4 , "over 9000" ) )))

and will the cell be blank
if so what happens
at the moment a blank cell will be 1

this will leave blank as blank
=If(D17="","", IF( D17 < 2500 , 1 , IF( D17 < 5000 , 2 , IF( D17 < 7500, 3 , IF( D17 <  = 9000 , 4 , "over 9000" ) ))))

or IFS()

=IFS(D17="","",D17<2500,1,D17<5000,2,   D17 <7500,3,D17<=9000,4,   TRUE,"error over 9000")


----------



## Bedford (Jan 1, 2023)

etaf said:


> so 9000 = 4
> 
> =IF( D17 < 2500 , 1 , IF( D17 < 5000 , 2 , IF( D17 < 7500, 3 , IF( D17 <  = 9000 , 4 , "over 9000" ) )))


Sorry, yes you are correct, 9000 = 4, more than 9000, for example 9001, is an error.


----------



## etaf (Jan 1, 2023)

ok, see last post


----------



## Bedford (Jan 1, 2023)

etaf said:


> ok, see last post


That works, thank you etaf!


----------



## etaf (Jan 1, 2023)

you are welcome


----------

