N Gauge Forum

Notices, Help With Problems and Your Forum Ideas... => Computer Help => Topic started by: TrevL on September 24, 2020, 08:46:08 PM

Title: Any "Excel" experts here?
Post by: TrevL on September 24, 2020, 08:46:08 PM
My inner nerd/OCD has kicked in.
I have a spreadsheet which I log my daily steps on :-[ and columns for cumalative totals for month and year.   I also have columns for daily, monthly and yearly miles, which I've used a formula to convert from steps to miles.
The problem is with the miles columns, where it works out the entries to five decimal places, which I even think that is far too many.  I'd like to limit those to two decimal places.  Is there an easy way to do this please?  TIA.
Title: Re: Any "Excel" experts here?
Post by: OffshoreAlan on September 24, 2020, 08:58:58 PM
Highlight the cell, then from the menu select Format/Cells.  Then select Number from the category list and you then get a screen giving the option of number of decimal places.
Title: Re: Any "Excel" experts here?
Post by: BRMan on September 24, 2020, 09:00:52 PM
Right click the cell concerned, select format cells and then select number and set the number of decimal places from the choice box.  Then when happy use format painter on all similar cells


Cheers,

BR Man
Title: Re: Any "Excel" experts here?
Post by: njee20 on September 24, 2020, 09:01:48 PM
Highlight the whole column when you do that, to ensure subsequent rows are still to 2dp.

Even easier highlight the column and find the button that looks like:

  0.00
->0.0

That'll just reduce the number of decimal places incrementally.
Title: Re: Any "Excel" experts here?
Post by: chrism on September 24, 2020, 09:03:36 PM
Quote from: BRMan on September 24, 2020, 09:00:52 PM
Right click the cell concerned, select format cells and then select number and set the number of decimal places from the choice box.  Then when happy use format painter on all similar cells

Easier to select all the cells required or, even, the entire column and choose the desired format from the menu.
Title: Re: Any "Excel" experts here?
Post by: BRMan on September 24, 2020, 09:04:24 PM
Nice one njee20, :)
Title: Re: Any "Excel" experts here?
Post by: Trainfish on September 24, 2020, 09:07:26 PM
The button @njee20 (https://www.ngaugeforum.co.uk/SMFN/index.php?action=profile;u=1147) refers to is shown below

(https://www.ngaugeforum.co.uk/SMFN/gallery/99/262-240920210613-996871790.jpeg)

It may be a lot smaller than my picture though of course
Title: Re: Any "Excel" experts here?
Post by: njee20 on September 24, 2020, 09:09:27 PM
Quote from: Trainfish on September 24, 2020, 09:07:26 PM
The button @njee20 (https://www.ngaugeforum.co.uk/SMFN/index.php?action=profile;u=1147) refers to is shown below

(https://www.ngaugeforum.co.uk/SMFN/gallery/99/262-240920210613-996871790.jpeg)

It may be a lot smaller than my picture though of course

Too many 0s in my version too, brain failing me in my old age! :doh:
Title: Re: Any "Excel" experts here?
Post by: Trainfish on September 24, 2020, 09:20:11 PM
For some reason I always click the wrong one first time so usually have to then click the other one twice or more  :doh:
Title: Re: Any "Excel" experts here?
Post by: njee20 on September 24, 2020, 09:57:01 PM
Yep, same, yet when I drew it I knew exactly which one it was! :doh:
Title: Re: Any "Excel" experts here?
Post by: TrevL on September 25, 2020, 08:30:09 AM
Thank you all who replied to this.  It has worked to an extent, but the initial column where the steps get converted to miles using a formula (=IMPRODUCT(D2,0.00042), that dosen't.  I just get a "chime" from the computer and it doesn't change.  Even if I try to do an individual cell, it refuses to comply.  Still, two out of three ain't bad, and when I enter new data, the last two columns stay at two decimal places, so pretty happy with that.
Thanks again Guys.
Title: Re: Any "Excel" experts here?
Post by: chrism on September 25, 2020, 08:58:38 AM
Quote from: TrevL on September 25, 2020, 08:30:09 AM
Thank you all who replied to this.  It has worked to an extent, but the initial column where the steps get converted to miles using a formula (=IMPRODUCT(D2,0.00042), that dosen't.  I just get a "chime" from the computer and it doesn't change.  Even if I try to do an individual cell, it refuses to comply.  Still, two out of three ain't bad, and when I enter new data, the last two columns stay at two decimal places, so pretty happy with that.
Thanks again Guys.

Try changing your formulae to;

=ROUND(IMPRODUCT(D2,0.00042),2)

That should do the trick.
Title: Re: Any "Excel" experts here?
Post by: TrevL on September 25, 2020, 09:31:45 AM
Awesome!  :claphappy:  That did the trick, Many thanks Chris :thumbsup: :thumbsup: :thumbsup: