General Betting
There is currently 1 person viewing this thread.
18 Feb 10
14:22
Joined:
Date Joined:
25 Jun 08
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
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
sort by:
Show
per page
Replies: 14
|
By:
use a lookup table. VLOOKUP. The excel help file will explain it for you.
|
|
By:
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. |
|
By:
Numbers in Cell A1
=IF(A11,0,ROUNDUP(A/1)) |
|
By:
=IF(A1,0,ROUNDUP(A/1))
|
|
By:
the smaller than 0 should be smaller than 1, otherwise 0 returns "1"
|
|
By:
my forumula is messed up meant to divide by 10 and roundup, but you get i mean.
|
|
By:
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 |
|
By:
I just made another column with =IF(B1=5,5,B1) and that works, not very clean, but it works.
|
|
By:
there's a greater than sign in there somewhere - should have figured it wouldn't come out from Lori's post above!
|
|
By:
=if(a1 is smaller than 1,0,if(a1 is greater than 40,5,INT(a1/10)+1))
|
|
By:
=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 |
|
By:
ffs, if greater than 40, not 51, sorry
|
|
By:
min(5,max(0,(INT(a1/10)+1)))
|
|
By:
Thanks again - especially Lori, got a better handle on using Ifs within Ifs now. Cheers
|