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
|