Return a value if..

[ajaay]

Hi.

I have a sheet that i need to set up so that if a value is less then 5000 it will return a number 4 and then if it is greater then 5000 it willl return the number 6. How does one do that? Thanks i have tried and failed and producing such a thing any help with this easy task would be great!

 

[djreiswig]

=IF(A1 < 5000,4,IF(A1 > 5000,6))

Replace the A1's with the cell you are evaluating.

 

[repairman615]

Hello,
Welcome!

What if it is 5000 even?

This handles for that, you might need to tweak it a bit:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 96pt" width=128><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd; WIDTH: 96pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=128>=IF(A1<=5000,4,6)</TD></TR></TBODY></TABLE>

 

[djreiswig]

=IF(A2 < 5000,4,IF(A2 > 5000,6,""))

This leaves the cell blank if the value in A1=5000. If you want something else then replace the "" with the proper value.

 

[ajaay]

What about if the cell doesn't have a value in it? will it return it as 0?

 

[repairman615]

This will, is that what you would like?

<TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=204><COLGROUP><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 3730" width=204><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 153pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=204>=IF(A1="",0,IF(A1<=5000,4,6))</TD></TR></TBODY></TABLE>

 

[T. Valko]

What about if the cell doesn't have a value in it? will it return it as 0?

If there is a number in the cell will it always be a positive number?

if a value is less then 5000 it will return a number 4 and then if it is greater then 5000 it willl return the number 6.

OK, what if the number IS 5000?

Instead of guessing at what you want I'd rather ask for clarification before I suggest something.

 

[ajaay]

If there is a number in the cell will it always be a positive number? Yes it will


OK, what if the number IS 5000? return 6

Instead of guessing at what you want I'd rather ask for clarification before I suggest something.

does that help? sorry i may not have put enough information in my opening question

 

[djreiswig]

If the cell is blank then it would have a value of 0 which is less than 5000 so it would return 4. If you need to check for blank cells, that would require another modification.

=IF(A1<>"",IF(A1<5000,4,IF(A1>5000,6,"")),"0")

This returns 0 if the cell is blank, and a blank cell if the cell equals 5000.

 

[repairman615]

Try this:

<TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=204><COLGROUP><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 3730" width=204><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 153pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=204>=IF(A1="",0,IF(A1<5000,4,6))</TD></TR></TBODY></TABLE>