Any "Excel" experts here?

Started by TrevL, September 24, 2020, 08:46:08 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

TrevL

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.
Cheers, Trev.


Time flys like an arrow, fruit flies like a banana!

OffshoreAlan

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.

BRMan

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

njee20

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.

chrism

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.

BRMan


Trainfish

The button @njee20 refers to is shown below



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

In April 2024 I will be raising money for Cancer Research UK by doing at least 100 press-ups every day.  Feel free to click on the picture to go to the donations page if you would like to help me to reach my target.



To follow the construction of my layout "Longcroft" from day 1, you'll have to catch the fish below first by clicking on it which isn't difficult right now as it's frozen!

<*))))><

njee20

Quote from: Trainfish on September 24, 2020, 09:07:26 PM
The button @njee20 refers to is shown below



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:

Trainfish

For some reason I always click the wrong one first time so usually have to then click the other one twice or more  :doh:
John

In April 2024 I will be raising money for Cancer Research UK by doing at least 100 press-ups every day.  Feel free to click on the picture to go to the donations page if you would like to help me to reach my target.



To follow the construction of my layout "Longcroft" from day 1, you'll have to catch the fish below first by clicking on it which isn't difficult right now as it's frozen!

<*))))><

njee20

Yep, same, yet when I drew it I knew exactly which one it was! :doh:

TrevL

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.
Cheers, Trev.


Time flys like an arrow, fruit flies like a banana!

chrism

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.

TrevL

Awesome!  :claphappy:  That did the trick, Many thanks Chris :thumbsup: :thumbsup: :thumbsup:
Cheers, Trev.


Time flys like an arrow, fruit flies like a banana!

Please Support Us!
March Goal: £100.00
Due Date: Mar 31
Total Receipts: £77.34
Below Goal: £22.66
Site Currency: GBP
77% 
March Donations