I need serious help with this formula regarding rounding a number please!!!

[Jaffabfc]

Hi,

Right i have a spreadsheet and in column j2 i have a formula of the following

"=l2/365*e2*2" which brings back the answer of 637. What i am wanting this to do is round up to 640, i can do this with no problem with the round formula.

What i want is that if the formula above equals say 2832 i want to be able to round this to 2800 and 2830, i want to be able to enter a formula and just drag it down.

I have in another cell the following "=IF(LEN(J2)=3,ROUND(J2,-1),IF(LEN(J2)=2,ROUND(J2,-1)))" which works if the answer in j2 is wrote it and not worked out by a formula.


How can i do it all in 1 cell and drag down (preferably j2 and drag down)

I just cant work it out.

thanks

 

[Eric Golf]

Hi,

I can't download files where I am. You could Round the result to an integer before checking length then e.g.

=ROUND(I2,(LEN(ROUND(I2,0))-2)*-1)

Eric

 

[KyleUK]

i want column j to say 640/350/380 i have a few 100 more that go upto 4 digits too

Are you wanting to round so that only the first 2 numbers apply? ie. 6712 becomes 6700 and 617 becomes 620?

If so:

=ROUND(J2,-LEN(INT(J2))+2)

 

[Jaffabfc]

Hi KyleUK,

That seems to work with most of them,thanks.

How do i get it so if i have say the number 72? and i want it to be 70?

 

[Jaffabfc]

or even 1 digit round to 10?

thanks

 

[KyleUK]

Messy, but this should work:

=ROUND(J2,-LEN(INT(ABS(J2)))+IF(LEN(INT(ABS(J2)))>2,2,LEN(INT(ABS(J2)))-1))

Will also cover negative numbers.

 

[Jaffabfc]

thats worked spot on, ive replaced j2 with the formula and kept it all in 1 cell.

Thanks all for your help.

 

[joeu2004]

How about this:

=IF(J2<100,ROUND(J2,-1),--TEXT(J2,"0.0E+0"))

 

[joeu2004]

How about this:
=IF(J2<100,ROUND(J2,-1),--TEXT(J2,"0.0E+0"))

More likely what you want is:

=IF(ABS(J2)<100,ROUND(TEXT(J2,"0E+0"),0),--TEXT(J2,"0.0E+0"))

Thus:
1. 3-digit numbers and longer are rounded to the their first 2 significant digits (xx0...0).
2. 2-digit numbers are rounded to their first significant digit (x0)
3. 1-digit numbers with possible fractional digits are rounded to 1 digit.
4. Numbers less than 0.5 are rounded to zero.
5. The above rules work with negative as well. Change ABS(J2) to J2 if J2 is always non-negative.