excel help please

use a lookup table. VLOOKUP. The excel help file will explain it for you.

I've got a spreadsheet which has a cell which has a number in that ranges from -99 to +199. If the number is negative, I want to return zero (in another cell). If the number is 1-10, I want to return 1, if its 11-20 I want to return 2, if its 21-30, 3, 31-40, 4, 41+, 5.

Does this require a massive complicated formula to do with a lot of ANDIFs and things like that? Is there an easy way to do it? TIA

=if(a1 is smaller than 0,0,INT(a1/10)+1)

There's no smaller than sign on betfair, so hope this make sense.

Numbers in Cell A1

=IF(A11,0,ROUNDUP(A/1))

=IF(A1,0,ROUNDUP(A/1))

the smaller than 0 should be smaller than 1, otherwise 0 returns "1"

my forumula is messed up meant to divide by 10 and roundup, but you get i mean.

That's great guys and thanks very much.

Problem is if its 41+, I want it to return 5, i.e. I want 5 to be the maximum that the function can return. At the moment if the number in my cell a1 is 56 it returns 6, for example.

Thanks again

I just made another column with =IF(B1=5,5,B1) and that works, not very clean, but it works.

there's a greater than sign in there somewhere - should have figured it wouldn't come out from Lori's post above!

=if(a1 is smaller than 1,0,if(a1 is greater than 40,5,INT(a1/10)+1))

=if(a1 is smaller than 1,0,if(a1 is greater than 51,5,INT(a1/10)+1))

How this works... it checks to see if a1 is smaller than 1, if so it returns the 0 after the comma.

If not, it does the second if statement... if a1 is greater than 51, it returns the 5 after the comma, if not then it does the INT (or rounding thing which looks better) part

ffs, if greater than 40, not 51, sorry

min(5,max(0,(INT(a1/10)+1)))

Thanks again - especially Lori, got a better handle on using Ifs within Ifs now. Cheers