Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Any MS Excel Guru's? - Help Please

  1. #1
    Join Date
    Jan 2007
    Location
    Yorkshire, UK
    Posts
    4,585

    Default Any MS Excel Guru's? - Help Please

    Hi

    I've just spent about three weeks building a MS Excel spreadsheet that calculates our retail price lists for our business (lots of work!).

    I've pretty much finished it, but I now realise that perhaps I should have rounded all retail prices to the nearest whole figure (ie. £70.31 would round to £70.00).

    Is there an easy way to globally round all the prices in one go by just selecting a column of cells, or do I have to use the 'round' function on each individual cell by individually editing it?

    Please tell me there's an easy way to do it

    Cheers

    Jem
    Quote: "There is a theory which states that if ever, for any reason, anyone discovers what exactly the Universe is for and why it is here it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another that states that this has already happened.”... Douglas Adams 1952 - 2001

  2. #2
    Join Date
    Dec 2006
    Location
    Essex, UK
    Posts
    8,648

    Default

    yes i think?

    right mouse on a cell, format cell, number, then under category select number, then on the right under decimal places put that down to 0
    Eat Sleep Lase Repeat

  3. #3
    Join Date
    Jan 2007
    Location
    Yorkshire, UK
    Posts
    4,585

    Default

    Just tried it. Thanks Andy, much appreciated.

    That works, is reasonable elegant and certainly does the job. Sometimes it's easy to miss the obvious

    O.K., so now i'm going to complicate matters a little further... The solution above gives me £70 (no decimal places). Is it possible to get £70.00 (two decimal places)?

    Not a problem at all if it isn't possible without a lot of effort as the first solution works fine. Just thought i'd ask... I know you like a challenge

    Cheers

    Jem
    Quote: "There is a theory which states that if ever, for any reason, anyone discovers what exactly the Universe is for and why it is here it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another that states that this has already happened.”... Douglas Adams 1952 - 2001

  4. #4
    Join Date
    Dec 2006
    Location
    Essex, UK
    Posts
    8,648

    Default

    um i dont know of a way, but thats not to say someone else might know a way.

    you might be able to setup an "if" formular to do that, someone else may chip in
    Eat Sleep Lase Repeat

  5. #5
    Join Date
    Aug 2008
    Location
    UK
    Posts
    5,704

    Default

    Yep its possible but only if you leave a gap between the column of figures cells and the autosum row. By that I mean the figures in the column must never enter the autosum row. ie if you move the autosu,m row you'll need to reformat. I'd leave plenty of spare rows myself.

    Let me explain, click on column of figures header and then right click > format cells > number > set decimal places to "0"

    Then go to the ROW set up for autosum. Click on the row number to highlight the row, then do the same as above and set the number of decimal places to "2".

    The column up to the row will now display "0" decimal places and the row across where the figures are added together by autosum will now display 2 decimal places.
    Last edited by White-Light; 08-24-2011 at 06:08.

  6. #6
    Join Date
    Aug 2008
    Location
    UK
    Posts
    5,704

    Default

    This is an example done that way Jem:

    Top figure is 10.35 but displays as 10.

    The bottom figure 42.35 is the auto sum but displays to 2 decimal places.



    Only thing to remember is that if you expand the product range at any time so you have to move the auto sum further down the sheet, then you'll need to reformat both column and row as I described above.

  7. #7
    Join Date
    Jan 2007
    Location
    Yorkshire, UK
    Posts
    4,585

    Default

    Ahh...

    Thanks Al, but there may be a slight misunderstanding here. I don't want just 'two decimal places', I want two decimal places with zero's (always rounded to the nearest £). For example I don't want £70.35, I want £70.00, or for £70.57, I want £71.00

    Again, Andy's solution works fine, I just thought it would look slightly more elegant if each whole number of £'s had two 'zero's' on the end.

    Cheers

    Jem
    Quote: "There is a theory which states that if ever, for any reason, anyone discovers what exactly the Universe is for and why it is here it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another that states that this has already happened.”... Douglas Adams 1952 - 2001

  8. #8
    Join Date
    Dec 2006
    Location
    Essex, UK
    Posts
    8,648

    Default

    if you go into the the autosum feature jem and search for rounding there is a round up and round down, but they are sepetare. so looks like you would need to do each cell seperate
    Eat Sleep Lase Repeat

  9. #9
    Join Date
    Jan 2007
    Location
    Yorkshire, UK
    Posts
    4,585

    Default

    Thanks Andy. Yeah I had a fiddle with Autosum earlier and have no problem using it. The problem is I have some very complicated formulas in some of the cells. It's taken me ages to set all the individual formulas and I don't fancy having to edit each cell just to round it so it looks pretty.

    I think i'll stick with your initial solution... Quick and simple

    Thanks again folks.

    Jem
    Quote: "There is a theory which states that if ever, for any reason, anyone discovers what exactly the Universe is for and why it is here it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another that states that this has already happened.”... Douglas Adams 1952 - 2001

  10. #10
    Join Date
    Aug 2008
    Location
    UK
    Posts
    5,704

    Default

    Just sub total in one row, then create the following formula in a grand total row:

    =ROUNDUP(A5,0)

    Where A5 is the cell reference for the sub total and "0" indicates the fact that you want it rounded up to the nearest whole number.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •