[SadiesMom]

I need help writing a forumla.
I have figures in column A. I need for Column B to round either up or down (to the nearest 1,000) based on wheter the figure in A is positve or negative.

Example:
Column A Column B
15,821,344.25 I need to round down to 15,821,000.00
<15,821,344.25> I need to round up to 15,822,000.00

Thanking you in advance.

[Advertisements]

To Round up

Click on your open box in Column A (the column you want to be rounding up)

Type, =ROUNDUP(

Click on the number you want to round up. (will show as D2, or F3, etc)

Type, ,-3)

Hit Enter

It should look something like this...

=ROUNDUP(D2,-3)



To Round down


Type, =ROUNDDOWN(

Click on your open box in Column B this time(the column you want to be rounding down)

Click on the number you want to round down. (will show as D2, F3, etc)

Type, ,-3)

Hit Enter

It should look something like this...

=ROUNDDOWN(D2,-3)

The number -3 is designated for how many spaces LEFT of the decimal point you want to round towards Zero. So if you wanted to round the next space you would put -4, or if you wanted only 2 spaces LEFT of the decimal point, you would put -2. See?

That should work for you, it works for me.

Let me know how things go.

Edited by Ferrari, 29 March 2009 - 11:08 PM.

[Ferrari]

To Round up

Click on your open box in Column A (the column you want to be rounding up)

Type, =ROUNDUP(

Click on the number you want to round up. (will show as D2, or F3, etc)

Type, ,-3)

Hit Enter

It should look something like this...

=ROUNDUP(D2,-3)



To Round down


Type, =ROUNDDOWN(

Click on your open box in Column B this time(the column you want to be rounding down)

Click on the number you want to round down. (will show as D2, F3, etc)

Type, ,-3)

Hit Enter

It should look something like this...

=ROUNDDOWN(D2,-3)

The number -3 is designated for how many spaces LEFT of the decimal point you want to round towards Zero. So if you wanted to round the next space you would put -4, or if you wanted only 2 spaces LEFT of the decimal point, you would put -2. See?

That should work for you, it works for me.

Let me know how things go.

Edited by Ferrari, 29 March 2009 - 11:08 PM.

[Vino Rosso]

And, if your cell could contain positive or negative numbers, you could combine the formulae posted by Ferrari...

=IF(A1>0,ROUNDUP(A1,-3),ROUNDDOWN(A1,-3))

[SadiesMom]

Thanks to everyone for your help. The IF formula was exactly what I needed. It works perfect!!!

[Ferrari]

Glad I could contribute

Good Luck!

[Vino Rosso]

Yep, well done Ferrari. You did the hard work. (Not so much luck in Melbourne, though.)

[Ferrari]

Thanks!