nested IF w/ LEN and ROUNDUP??

[LTUser54]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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?

 

[Bob Phillips]

Me too. You could also try

=CEILING(A1*2,1000)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[LTUser54]

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