!!

Not Registered?

Welcome!  Please register to view all of the new posts and forum boards - some of which are hidden to guests.  After registering and gaining 10 posts you will be able to sell and buy items on our N'porium.

If you have any problems registering, then please check your spam filter before emailing us.  Hotmail users seem to find their emails in the Junk folder.


Thanks for reading,
The NGF Staff.

Author Topic: Any "Excel" experts here?  (Read 398 times)

0 Members and 1 Guest are viewing this topic.

Offline TrevL

  • Trade Count: (0)
  • Full Member
  • ***
  • N Gauge Society Number: 26716
  • Posts: 538
  • Country: gb
  • Gender: Male
    • Awards
Any "Excel" experts here?
« 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.
Cheers, Trev.


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

Offline OffshoreAlan

  • Trade Count: (0)
  • Full Member
  • ***
  • N Gauge Society Number: 26950
  • Posts: 93
  • Country: england
  • Gender: Male
    • Awards
Re: Any "Excel" experts here?
« Reply #1 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.

Offline BRMan

  • Trade Count: (+1)
  • Full Member
  • ***
  • Posts: 69
  • Country: gb
    • Awards
Re: Any "Excel" experts here?
« Reply #2 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

Online njee20

  • Trade Count: (+4)
  • Full Member
  • ***
  • N Gauge Society Number: 22598
  • Posts: 7282
  • Country: gb
    • Awards
Re: Any "Excel" experts here?
« Reply #3 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.

Online chrism

  • Trade Count: (+1)
  • Full Member
  • ***
  • Posts: 1911
  • Country: gb
  • The Coniston Railway
    • Awards
Re: Any "Excel" experts here?
« Reply #4 on: September 24, 2020, 09:03:36 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.

Offline BRMan

  • Trade Count: (+1)
  • Full Member
  • ***
  • Posts: 69
  • Country: gb
    • Awards
Re: Any "Excel" experts here?
« Reply #5 on: September 24, 2020, 09:04:24 PM »
Nice one njee20, :)

Online Trainfish

  • Larger Gallery
  • Trade Count: (+11)
  • Full Member
  • ***
  • N Gauge Society Number: 21524
  • Posts: 2704
  • Country: england
  • Gender: Male
  • IPAholic
    • Ebay
    • YouTube
    • My layout, Longcroft under construction
    • Awards
Re: Any "Excel" experts here?
« Reply #6 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
John

To see my layout "Longcroft" which is currently under construction, you'll have to catch the fish below first by clicking on it!

<*))))><

See my Youtube video channel          >>>>>>>> here <<<<<<<<          >>>>>>>> or here <<<<<<<<          >>>>>>>> or here <<<<<<<<

Online njee20

  • Trade Count: (+4)
  • Full Member
  • ***
  • N Gauge Society Number: 22598
  • Posts: 7282
  • Country: gb
    • Awards
Re: Any "Excel" experts here?
« Reply #7 on: September 24, 2020, 09:09:27 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:

Online Trainfish

  • Larger Gallery
  • Trade Count: (+11)
  • Full Member
  • ***
  • N Gauge Society Number: 21524
  • Posts: 2704
  • Country: england
  • Gender: Male
  • IPAholic
    • Ebay
    • YouTube
    • My layout, Longcroft under construction
    • Awards
Re: Any "Excel" experts here?
« Reply #8 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:
John

To see my layout "Longcroft" which is currently under construction, you'll have to catch the fish below first by clicking on it!

<*))))><

See my Youtube video channel          >>>>>>>> here <<<<<<<<          >>>>>>>> or here <<<<<<<<          >>>>>>>> or here <<<<<<<<

Online njee20

  • Trade Count: (+4)
  • Full Member
  • ***
  • N Gauge Society Number: 22598
  • Posts: 7282
  • Country: gb
    • Awards
Re: Any "Excel" experts here?
« Reply #9 on: September 24, 2020, 09:57:01 PM »
Yep, same, yet when I drew it I knew exactly which one it was! :doh:

Offline TrevL

  • Trade Count: (0)
  • Full Member
  • ***
  • N Gauge Society Number: 26716
  • Posts: 538
  • Country: gb
  • Gender: Male
    • Awards
Re: Any "Excel" experts here?
« Reply #10 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.
Cheers, Trev.


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

Online chrism

  • Trade Count: (+1)
  • Full Member
  • ***
  • Posts: 1911
  • Country: gb
  • The Coniston Railway
    • Awards
Re: Any "Excel" experts here?
« Reply #11 on: September 25, 2020, 08:58:38 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.

Offline TrevL

  • Trade Count: (0)
  • Full Member
  • ***
  • N Gauge Society Number: 26716
  • Posts: 538
  • Country: gb
  • Gender: Male
    • Awards
Re: Any "Excel" experts here?
« Reply #12 on: September 25, 2020, 09:31:45 AM »
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!
October Goal: £60.00
Due Date: Oct 31
Total Receipts: £210.00
Above Goal: £150.00
Site Currency: GBP
350% 
October Donations

SimplePortal 2.3.5 © 2008-2012, SimplePortal