L
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?
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?