New Posts For Thread: excel help pleasehttps://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-pleaseI'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,Thanks again - especially Lori, got a better handle on using Ifs within Ifs now. Cheershttps://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274902#420274902Thanks again - especially Lori, got a better handle on using Ifs within Ifs now. CheersFri, 19 Feb 2010 00:08:00 -0600min(5,max(0,(INT(a1/10)+1)))https://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274810#420274810min(5,max(0,(INT(a1/10)+1)))Thu, 18 Feb 2010 11:12:55 -0600ffs, if greater than 40, not 51, sorryhttps://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274794#420274794ffs, if greater than 40, not 51, sorryThu, 18 Feb 2010 08:55:43 -0600=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 thehttps://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274790#420274790=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 theThu, 18 Feb 2010 08:55:19 -0600=if(a1 is smaller than 1,0,if(a1 is greater than 40,5,INT(a1/10)+1))https://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274786#420274786=if(a1 is smaller than 1,0,if(a1 is greater than 40,5,INT(a1/10)+1))Thu, 18 Feb 2010 08:54:20 -0600there's a greater than sign in there somewhere - should have figured it wouldn't come out from Lori's post above!https://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274782#420274782there's a greater than sign in there somewhere - should have figured it wouldn't come out from Lori's post above!Thu, 18 Feb 2010 08:52:45 -0600I just made another column with =IF(B1=5,5,B1) and that works, not very clean, but it works.https://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274778#420274778I just made another column with =IF(B1=5,5,B1) and that works, not very clean, but it works.Thu, 18 Feb 2010 08:52:20 -0600That'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 againhttps://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274774#420274774That'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 againThu, 18 Feb 2010 08:46:37 -0600my forumula is messed up meant to divide by 10 and roundup, but you get i mean.https://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274770#420274770my forumula is messed up meant to divide by 10 and roundup, but you get i mean.Thu, 18 Feb 2010 08:41:10 -0600the smaller than 0 should be smaller than 1, otherwise 0 returns "1"https://community.betfair.com/general_betting/go/thread/view/94082/23518822/excel-help-please?post_id=420274766#420274766the smaller than 0 should be smaller than 1, otherwise 0 returns "1"Thu, 18 Feb 2010 08:40:08 -0600