Page 1 of 2 12 LastLast
Results 1 to 16 of 19

Thread: Calling all Excel wizards

  1. #1
    =10th=(E)SFC Mr_Tumble Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,174
    Thanks
    263
    Thanked
    300 times in 205 posts

    Calling all Excel wizards

    Morning folks,

    Got a bit of a problem with a spreadsheet I'm trying to build. I have to record mileage for work and I've got the thing set out so it calculates the total mileage each day if I put the start and finish in, adds it to the week total, and fires that total into a box down below. It also at the moment calculates personal mileage, by dint of taking anything in the weekend boxes and adding that total together and putting it in another box. Then the personal is taken away from the total to give me my business miles. This is only good for the weekends though. If I use the car outside of work during the week, it throws it all into confusion. I want to be able the put "private" or something, somewhere in the box for the day, that tells the sheet to add that day's total to the personal total.

    This is how the table's laid out:


    Any suggestions gratefully received

    Quote Originally Posted by The Quentos
    "My udder is growing. Quick pass me the parsely sauce." Said Oliver.

  2. #2
    Senior Member AETAaAS's Avatar
    Join Date
    May 2013
    Location
    Warrington
    Posts
    576
    Thanks
    72
    Thanked
    131 times in 117 posts
    • AETAaAS's system
      • Motherboard:
      • Asus Z97I Plus
      • CPU:
      • i7 4790k
      • Memory:
      • 16GB Vengeance 1866
      • Storage:
      • Crucial MX300 750GB
      • Graphics card(s):
      • inno3D GTX970 OC
      • PSU:
      • Silverstone ST45SF v3.0
      • Case:
      • Coolermaster N200
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • HP Envy 32

    Re: Calling all Excel wizards

    Doing it like this makes sense to me;


    You can separate personal and work mileage line by line (like lines 2-4 in the example below). Or you can combine them together into one line if you use the Trip function on your cars odometer to measure your personal or work driving per day (like line 6).



    So say you trip your personal driving, you can take that away from the total driving. Work miles = [(End miles - Start miles) - Personal]. Or Coulumn D - C - E in the example I've given.

  3. #3
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Manchester
    Posts
    13,812
    Thanks
    1,090
    Thanked
    1,971 times in 1,649 posts
    • scaryjim's system
      • Motherboard:
      • HP Pavilion
      • CPU:
      • A10 4600M
      • Memory:
      • 2x 4GB DDR3-1600 SODIMM
      • Storage:
      • 1TB HDD
      • Graphics card(s):
      • Radeon HD7660G (IGP)
      • PSU:
      • Battery/HP 19v brick
      • Case:
      • HP Pavilion G6
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1366x768 laptop panel

    Re: Calling all Excel wizards

    OK, my first suggestion would probably be to use Access, but I don't think that's what you're looking for Also if this is for mileage claims it's kind of financial so it's one of the rare occasions a spreadsheet might actually be the right tool!

    In Excel, this looks like a job for VLOOKUP - I don't have the reference handy but iirc vlookup (as the name implies) looks down one column for a particular value and then returns value of another column on the same row. I believe you can use it in array formulae to make it add things up automatically too.

    My only concern is that I can't really work out how your data is structured. I'd expect at least one side of your table to have 7 cells (one for each day) if it's a weekly recording schema. I'd probably also create a template and start a new sheet for each week (actually, that's a lie, I'd do the whole thing in Access because I'm a database guy really ).

    If you can clarify what the cells are meant to show in your screenshot I'll have a play later and see if I can come up with something...

  4. Received thanks from:

    Tumble (10-05-2017)

  5. #4
    =10th=(E)SFC Mr_Tumble Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,174
    Thanks
    263
    Thanked
    300 times in 205 posts

    Re: Calling all Excel wizards

    That was just a rough thing I knocked up last night - I 'll do a screenshot of the actual table in a bit

    EDIT:

    Here's a bit of the actual sheet. The weeks are stacked on top of each other across the sheet.


    Access isn't an option unfortunately, as I don't have it/can't install it on my computer.
    Last edited by Tumble; 09-05-2017 at 01:50 PM.

    Quote Originally Posted by The Quentos
    "My udder is growing. Quick pass me the parsely sauce." Said Oliver.

  6. #5
    Senior Member MaddAussie's Avatar
    Join Date
    Dec 2006
    Location
    Deepest Darkest Dorset
    Posts
    1,645
    Thanks
    542
    Thanked
    273 times in 165 posts
    • MaddAussie's system
      • Motherboard:
      • Gigabyte H55N-USB3 (MITX)
      • CPU:
      • i7 870 (S1156)
      • Memory:
      • 8GB
      • Storage:
      • 120GB Corsair SSD 2TB Samsung
      • Graphics card(s):
      • GTX 580
      • Case:
      • Silverstone SG07B
      • Operating System:
      • Win 7 Utli
      • Monitor(s):
      • 24" Dell HD + Viewsonic 17"

    Re: Calling all Excel wizards

    I'd use SUMIF

    Basic syntax is SUMIF(range, criteria, [sum_range])



    In your case the formula would look something like this:


    =SUMIF(C2:C4,"<>Private",B2:B4)


    Isn't salad what food eats??

  7. Received thanks from:

    Tumble (10-05-2017)

  8. #6
    Senior Member AETAaAS's Avatar
    Join Date
    May 2013
    Location
    Warrington
    Posts
    576
    Thanks
    72
    Thanked
    131 times in 117 posts
    • AETAaAS's system
      • Motherboard:
      • Asus Z97I Plus
      • CPU:
      • i7 4790k
      • Memory:
      • 16GB Vengeance 1866
      • Storage:
      • Crucial MX300 750GB
      • Graphics card(s):
      • inno3D GTX970 OC
      • PSU:
      • Silverstone ST45SF v3.0
      • Case:
      • Coolermaster N200
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • HP Envy 32

    Re: Calling all Excel wizards

    Oops sorry I misread your post and thought you were going to start logging. Didn't realize you had the spreadsheet already setup.

  9. Received thanks from:

    Tumble (10-05-2017)

  10. #7
    Senior Member
    Join Date
    Nov 2009
    Posts
    3,965
    Thanks
    938
    Thanked
    488 times in 381 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i3-540, Prolimatech Megahalems, Akasa Apache 120mm
      • Memory:
      • 4x2GB Corsair XMS3 1600 9-9-9-24
      • Storage:
      • 256GB Samsung 840-Pro, 60GB OCZ Vertex 2, 1TB Seagate 7200.12, 1TB Seagate ES.2
      • Graphics card(s):
      • Gigabyte GTX 460 1GB SuperOverClocked
      • PSU:
      • NZXT Hale 90 750w
      • Case:
      • BitFenix Survivor + Bitfenix spectre LED fans, LG BluRay R/W optical drive
      • Operating System:
      • Windows 7 Professional
      • Monitor(s):
      • Dell U2311h 1920x1080
      • Internet:
      • 200Mb/s Fibre and 4G wifi

    Re: Calling all Excel wizards

    sumif (or even just a standard if routine) is the way to go. Vlookup is a different beast.

  11. Received thanks from:

    Tumble (10-05-2017)

  12. #8
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Manchester
    Posts
    13,812
    Thanks
    1,090
    Thanked
    1,971 times in 1,649 posts
    • scaryjim's system
      • Motherboard:
      • HP Pavilion
      • CPU:
      • A10 4600M
      • Memory:
      • 2x 4GB DDR3-1600 SODIMM
      • Storage:
      • 1TB HDD
      • Graphics card(s):
      • Radeon HD7660G (IGP)
      • PSU:
      • Battery/HP 19v brick
      • Case:
      • HP Pavilion G6
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1366x768 laptop panel

    Re: Calling all Excel wizards

    Quote Originally Posted by MaddAussie View Post
    I'd use SUMIF

    Basic syntax is SUMIF(range, criteria, [sum_range])
    Heh, that's a new one on me but seems to be exactly what you're looking for.

    Quote Originally Posted by ik9000 View Post
    sumif (or even just a standard if routine) is the way to go. Vlookup is a different beast.
    Didn't realise I was so out of date - must spend some time with Office 2007+ catching up on anything I've missed!

    iirc back in Office '97 VLOOKUP and array formulae were the only way to achieve this; I hope so anyway ... hard to believe I'd've done it the hard way if SUMIF was available back then. I've not done much with Excel since then; my recent Office experience is mostly Access...

  13. Received thanks from:

    Tumble (10-05-2017)

  14. #9
    Senior Member
    Join Date
    Nov 2009
    Posts
    3,965
    Thanks
    938
    Thanked
    488 times in 381 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i3-540, Prolimatech Megahalems, Akasa Apache 120mm
      • Memory:
      • 4x2GB Corsair XMS3 1600 9-9-9-24
      • Storage:
      • 256GB Samsung 840-Pro, 60GB OCZ Vertex 2, 1TB Seagate 7200.12, 1TB Seagate ES.2
      • Graphics card(s):
      • Gigabyte GTX 460 1GB SuperOverClocked
      • PSU:
      • NZXT Hale 90 750w
      • Case:
      • BitFenix Survivor + Bitfenix spectre LED fans, LG BluRay R/W optical drive
      • Operating System:
      • Windows 7 Professional
      • Monitor(s):
      • Dell U2311h 1920x1080
      • Internet:
      • 200Mb/s Fibre and 4G wifi

    Re: Calling all Excel wizards

    sumif was around in office 2000, I think it was there in 97 too.

  15. #10
    =10th=(E)SFC Mr_Tumble Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,174
    Thanks
    263
    Thanked
    300 times in 205 posts

    Re: Calling all Excel wizards

    I've tried the SUMIF - I can't make it work though Clearly I'm doing it all wrong....

    Quote Originally Posted by The Quentos
    "My udder is growing. Quick pass me the parsely sauce." Said Oliver.

  16. #11
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Manchester
    Posts
    13,812
    Thanks
    1,090
    Thanked
    1,971 times in 1,649 posts
    • scaryjim's system
      • Motherboard:
      • HP Pavilion
      • CPU:
      • A10 4600M
      • Memory:
      • 2x 4GB DDR3-1600 SODIMM
      • Storage:
      • 1TB HDD
      • Graphics card(s):
      • Radeon HD7660G (IGP)
      • PSU:
      • Battery/HP 19v brick
      • Case:
      • HP Pavilion G6
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1366x768 laptop panel

    Re: Calling all Excel wizards

    Quote Originally Posted by ik9000 View Post
    sumif was around in office 2000, I think it was there in 97 too.
    Oops, better hand my "official MS Office goto person" badge in to my boss...

  17. Received thanks from:

    Tumble (10-05-2017)

  18. #12
    Senior Member
    Join Date
    Nov 2009
    Posts
    3,965
    Thanks
    938
    Thanked
    488 times in 381 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i3-540, Prolimatech Megahalems, Akasa Apache 120mm
      • Memory:
      • 4x2GB Corsair XMS3 1600 9-9-9-24
      • Storage:
      • 256GB Samsung 840-Pro, 60GB OCZ Vertex 2, 1TB Seagate 7200.12, 1TB Seagate ES.2
      • Graphics card(s):
      • Gigabyte GTX 460 1GB SuperOverClocked
      • PSU:
      • NZXT Hale 90 750w
      • Case:
      • BitFenix Survivor + Bitfenix spectre LED fans, LG BluRay R/W optical drive
      • Operating System:
      • Windows 7 Professional
      • Monitor(s):
      • Dell U2311h 1920x1080
      • Internet:
      • 200Mb/s Fibre and 4G wifi

    Re: Calling all Excel wizards

    sorry, I don't have a lot of time atm, but as a stop gap just use a standard if function if(B3="private",,A3*whatever you want to do with it) to populate a column from which you can use a simple sum() function at the bottom to get the cumulative total

  19. Received thanks from:

    Tumble (10-05-2017)

  20. #13
    ɯʎɔɐɹsɐʌʍ mycarsavw's Avatar
    Join Date
    Feb 2007
    Posts
    4,910
    Thanks
    1,066
    Thanked
    649 times in 479 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 8Gb Vengeance Low Profile
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10 and it isn't crap
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky and it also isn't crap, who'd have thought.

    Re: Calling all Excel wizards

    This assumes you record all private mileage with the same phrase, 'private'



    The formula in O9 (the Private mileage weekly subtotal) is;

    =SUMIF($A$3:$L$9,N9,(B3:B9))

    which searches A3 to L9, looks for the word defined in N9 and sums all instances in the adjacent column.

    The formula in O8 just subtracts the weekly private mileage from total weekly mileage in M12.

    For the annual totals just use another SUMIF;

    Business =SUMIF($N$5:$N$26,N28,$O$5:$O$26)

    Private =SUMIF($N$5:$N$26,N29,$O$5:$O$26)
    |scaryjim:"frankly any extra width would be a godsend to me..." |
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|
    |razer121:(discussing his ex after sleeping with her sister)"...im more of a family guy..."|

  21. Received thanks from:

    Tumble (10-05-2017)

  22. #14
    =10th=(E)SFC Mr_Tumble Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,174
    Thanks
    263
    Thanked
    300 times in 205 posts

    Re: Calling all Excel wizards

    That's cool.... I have managed to do something, but I had to make a cell for every total, and add that column together, and then hide that column Your solution is a lot more elegant! Question though: What does the $ do?

    Quote Originally Posted by The Quentos
    "My udder is growing. Quick pass me the parsely sauce." Said Oliver.

  23. #15
    Senior Member
    Join Date
    Nov 2009
    Posts
    3,965
    Thanks
    938
    Thanked
    488 times in 381 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i3-540, Prolimatech Megahalems, Akasa Apache 120mm
      • Memory:
      • 4x2GB Corsair XMS3 1600 9-9-9-24
      • Storage:
      • 256GB Samsung 840-Pro, 60GB OCZ Vertex 2, 1TB Seagate 7200.12, 1TB Seagate ES.2
      • Graphics card(s):
      • Gigabyte GTX 460 1GB SuperOverClocked
      • PSU:
      • NZXT Hale 90 750w
      • Case:
      • BitFenix Survivor + Bitfenix spectre LED fans, LG BluRay R/W optical drive
      • Operating System:
      • Windows 7 Professional
      • Monitor(s):
      • Dell U2311h 1920x1080
      • Internet:
      • 200Mb/s Fibre and 4G wifi

    Re: Calling all Excel wizards

    Quote Originally Posted by Tumble View Post
    That's cool.... I have managed to do something, but I had to make a cell for every total, and add that column together, and then hide that column Your solution is a lot more elegant! Question though: What does the $ do?
    $ fix a range as an absolute reference, so it doesn't change as you drag to copy formula into other cells. It fixes the part of the reference following it.

    eg set up a simple sheet
    numbers 1-5 down column A, 2-6 down column B in cell C1 type the formula C1=A1*B1 then copy and paste this formula to the other cells below, and across to columnD too you will see the formula changes in each cell so C2=A2*B2, D3=B3*C3 etc

    But if you use a C1=$A1*B$1 and then copy and paste that formula you will see in the other cells that C2=$A2*B$1 and D3=$A3*C$1 If you used $A$1 the reference would not change at all.

    It is how you can reference one cell (or a range of cells) in a formula which you need to replicate down several rows/across several columns, without the target reference cell/range moving.

    Google "Excel Absolute Reference" for more information

  24. Received thanks from:

    Tumble (11-05-2017)

  25. #16
    Registered User
    Join Date
    May 2017
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: Calling all Excel wizards

    why when I enter the number 1.0615 it turns into a date?

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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