| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nested IF w/ LEN and ROUNDUP??
This is driving me nuts. I have a column of numbers. they are various 5 or 6 digit numbers. I want a single formula that will double the number and then round it up to the next 1000, however, when I use one formula using LEN and 2 (or 3) with just one formula it gives me a wrong number for some of the column numbers as a result. Here's what I ended up using: =ROUNDUP(B3*2,3-LEN(INT(B3))) this works correctly for any column number over six digits, i.e.: 100,150 =ROUNDUP(B3*2,2-LEN(INT(B3))) this works correctly for any column number UNDER six digits, i.e.: 90,150 BUT, when I try to put the two formulas together using IF, I get nowhere. I can paste in the right formula, eyeballing the number in the column, but that's a pain, and pretty slow. I'm trying to find a single formula that will work for a column number that is either 5 or 6 digits long. Any suggestions? -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=542682 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nested IF w/ LEN and ROUNDUP??
Maybe I'm not understanding what you want, but I think you're making it more
complicated that it needs to be. Does this work? =ROUNDUP(B3*2,-3) HTH, Elkar "LTUser54" wrote: This is driving me nuts. I have a column of numbers. they are various 5 or 6 digit numbers. I want a single formula that will double the number and then round it up to the next 1000, however, when I use one formula using LEN and 2 (or 3) with just one formula it gives me a wrong number for some of the column numbers as a result. Here's what I ended up using: =ROUNDUP(B3*2,3-LEN(INT(B3))) this works correctly for any column number over six digits, i.e.: 100,150 =ROUNDUP(B3*2,2-LEN(INT(B3))) this works correctly for any column number UNDER six digits, i.e.: 90,150 BUT, when I try to put the two formulas together using IF, I get nowhere. I can paste in the right formula, eyeballing the number in the column, but that's a pain, and pretty slow. I'm trying to find a single formula that will work for a column number that is either 5 or 6 digits long. Any suggestions? -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=542682 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nested IF w/ LEN and ROUNDUP??
Thanks for the suggestion, but I already tried that - it works fine if the
column number is 99,999 to 10,000 - fails miserably when the column number is 100,000 to 999,999. Try it yourself if you doubt me. I need a conditional formula and tried many versions with no luck. HELP!!!! Mark "Elkar" wrote: Maybe I'm not understanding what you want, but I think you're making it more complicated that it needs to be. Does this work? =ROUNDUP(B3*2,-3) HTH, Elkar "LTUser54" wrote: This is driving me nuts. I have a column of numbers. they are various 5 or 6 digit numbers. I want a single formula that will double the number and then round it up to the next 1000, however, when I use one formula using LEN and 2 (or 3) with just one formula it gives me a wrong number for some of the column numbers as a result. Here's what I ended up using: =ROUNDUP(B3*2,3-LEN(INT(B3))) this works correctly for any column number over six digits, i.e.: 100,150 =ROUNDUP(B3*2,2-LEN(INT(B3))) this works correctly for any column number UNDER six digits, i.e.: 90,150 BUT, when I try to put the two formulas together using IF, I get nowhere. I can paste in the right formula, eyeballing the number in the column, but that's a pain, and pretty slow. I'm trying to find a single formula that will work for a column number that is either 5 or 6 digits long. Any suggestions? -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=542682 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nested IF w/ LEN and ROUNDUP??
I did try it, and it seems to work just fine. So, perhaps I need a
clarification on your desired results. As I understand it, you want to take a number, multiply by 2, then round up to the nearest thousand. Using the formula =ROUNDUP(B3*2,-3), these are the results I get: 10000 = 20000 10001 = 21000 50000 = 100000 99999 = 200000 100000 = 200000 123456 = 247000 999999 = 2000000 Is this not what you're looking for? "Brainless_in_Boston" wrote: Thanks for the suggestion, but I already tried that - it works fine if the column number is 99,999 to 10,000 - fails miserably when the column number is 100,000 to 999,999. Try it yourself if you doubt me. I need a conditional formula and tried many versions with no luck. HELP!!!! Mark "Elkar" wrote: Maybe I'm not understanding what you want, but I think you're making it more complicated that it needs to be. Does this work? =ROUNDUP(B3*2,-3) HTH, Elkar "LTUser54" wrote: This is driving me nuts. I have a column of numbers. they are various 5 or 6 digit numbers. I want a single formula that will double the number and then round it up to the next 1000, however, when I use one formula using LEN and 2 (or 3) with just one formula it gives me a wrong number for some of the column numbers as a result. Here's what I ended up using: =ROUNDUP(B3*2,3-LEN(INT(B3))) this works correctly for any column number over six digits, i.e.: 100,150 =ROUNDUP(B3*2,2-LEN(INT(B3))) this works correctly for any column number UNDER six digits, i.e.: 90,150 BUT, when I try to put the two formulas together using IF, I get nowhere. I can paste in the right formula, eyeballing the number in the column, but that's a pain, and pretty slow. I'm trying to find a single formula that will work for a column number that is either 5 or 6 digits long. Any suggestions? -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=542682 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nested IF w/ LEN and ROUNDUP??
Me too. You could also try
=CEILING(A1*2,1000) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Elkar" wrote in message ... I did try it, and it seems to work just fine. So, perhaps I need a clarification on your desired results. As I understand it, you want to take a number, multiply by 2, then round up to the nearest thousand. Using the formula =ROUNDUP(B3*2,-3), these are the results I get: 10000 = 20000 10001 = 21000 50000 = 100000 99999 = 200000 100000 = 200000 123456 = 247000 999999 = 2000000 Is this not what you're looking for? "Brainless_in_Boston" wrote: Thanks for the suggestion, but I already tried that - it works fine if the column number is 99,999 to 10,000 - fails miserably when the column number is 100,000 to 999,999. Try it yourself if you doubt me. I need a conditional formula and tried many versions with no luck. HELP!!!! Mark "Elkar" wrote: Maybe I'm not understanding what you want, but I think you're making it more complicated that it needs to be. Does this work? =ROUNDUP(B3*2,-3) HTH, Elkar "LTUser54" wrote: This is driving me nuts. I have a column of numbers. they are various 5 or 6 digit numbers. I want a single formula that will double the number and then round it up to the next 1000, however, when I use one formula using LEN and 2 (or 3) with just one formula it gives me a wrong number for some of the column numbers as a result. Here's what I ended up using: =ROUNDUP(B3*2,3-LEN(INT(B3))) this works correctly for any column number over six digits, i.e.: 100,150 =ROUNDUP(B3*2,2-LEN(INT(B3))) this works correctly for any column number UNDER six digits, i.e.: 90,150 BUT, when I try to put the two formulas together using IF, I get nowhere. I can paste in the right formula, eyeballing the number in the column, but that's a pain, and pretty slow. I'm trying to find a single formula that will work for a column number that is either 5 or 6 digits long. Any suggestions? -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=542682 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nested IF w/ LEN and ROUNDUP??
Elkar - My apologies. I used your formula and it does work beautifully, and it's much cleaner than the one I cobbled together. I don't know what I was thinking yesterday. I was a bit at my wits end, though. And Mr. Bob Phillips.... Thank you SO MUCH for that CEILING function. Very cool. Once again you have proven your expertise to me and all others who monitor your posts. Thanks again, I love that CEILING function. Mark Boston, MA -- LTUser54 ------------------------------------------------------------------------ LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459 View this thread: http://www.excelforum.com/showthread...hreadid=542682 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
