Excel help

[CrazyEyez]

I need help coming up with a formula.
Cell A1 contains any number > 0.
How do I make B1 =
1 if A1 < 11
2 if 10 < A1 < 21
3 if 20 < A1 < 31
etc
?

In other words, value of B1 increases by 1 everytime A1 increases by 10.
Thanks.

[Soul Daddy]

=IF(A1<11,1,IF(A1<21,2,IF(A1<31,3,0)))

[Nottom]

A quick look up in the help file gives the QUOTIENT function.

This is just basic integer division.

Syntax is QUOTIENT(numerator,denominator) so in your case B1=QUOTIENT(A1, 10)

edit: this requires an add on to be installed, if you don't want to do that you can just use the INT function which rounds down to the nearest integer. INT(A1/10) should give you what you want.

[RacersEdge]

I think B1= CEILING(A1/10,1) gets it.

[CrazyEyez]

[ QUOTE ]
A quick look up in the help file gives the QUOTIENT function.

This is just basic integer division.

Syntax is QUOTIENT(numerator,denominator) so in your case B1=QUOTIENT(A1, 10)

edit: this requires an add on to be installed, if you don't want to do that you can just use the INT function which rounds down to the nearest integer. INT(A1/10) should give you what you want.

[/ QUOTE ]

INT'll do it. Thanks a lot.

Edit: CEILING is actually the better choice for me. Thanks Racer.

[Nottom]

[ QUOTE ]
I think B1= CEILING(A1/10,1) gets it.

[/ QUOTE ]

Oops, yeah this is right. Mine rounded down you want it to round up

(of course you could just add 1 to my answer /images/graemlins/smile.gif )