Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

EXCEL


  • Please log in to reply

#1
SadiesMom

SadiesMom

    New Member

  • Member
  • Pip
  • 8 posts
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.
  • 0

Advertisements


#2
Ferrari

Ferrari

    PC SURGEON

  • Member
  • PipPipPipPip
  • 1,939 posts
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.

  • 0

#3
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
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))
  • 0

#4
SadiesMom

SadiesMom

    New Member

  • Topic Starter
  • Member
  • Pip
  • 8 posts
Thanks to everyone for your help. The IF formula was exactly what I needed. It works perfect!!!
  • 0

#5
Ferrari

Ferrari

    PC SURGEON

  • Member
  • PipPipPipPip
  • 1,939 posts
Glad I could contribute :)

Good Luck!
  • 0

#6
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Yep, well done Ferrari. You did the hard work. (Not so much luck in Melbourne, though.)
  • 0

#7
Ferrari

Ferrari

    PC SURGEON

  • Member
  • PipPipPipPip
  • 1,939 posts
Thanks!
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP