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

Thread: Calling all Excel wizards

  1. #1
    Senior Member Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,217
    Thanks
    271
    Thanked
    315 times in 217 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
    Supermarket Generic Brand AETAaAS's Avatar
    Join Date
    May 2013
    Location
    Merseyside
    Posts
    654
    Thanks
    79
    Thanked
    147 times in 129 posts
    • AETAaAS's system
      • Motherboard:
      • MSI B450M Gaming Plus
      • CPU:
      • AMD Ryzen 2600
      • Memory:
      • 16GB Vengeance 3000
      • Storage:
      • Intel 660p 1TB
      • Graphics card(s):
      • EVGA 1080TI SC2
      • PSU:
      • Seasonic Focus 850W
      • Case:
      • Fractal Design Focus G
      • Operating System:
      • Windows 10 x64
      • Monitor(s):
      • HP Envy 32
      • Internet:
      • 17mbps

    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
    Gateshead
    Posts
    15,196
    Thanks
    1,231
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p 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
    Senior Member Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,217
    Thanks
    271
    Thanked
    315 times in 217 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,708
    Thanks
    628
    Thanked
    297 times in 179 posts
    • MaddAussie's system
      • Motherboard:
      • Asus ROG Strix Z370G
      • CPU:
      • i7 8700k (5.1Ghz)
      • Memory:
      • 16Gb
      • Storage:
      • 500G 960 EVO NMVE
      • Graphics card(s):
      • GTX 1070
      • PSU:
      • Corsair RM650i
      • Case:
      • Corsair Carbide A1r 240
      • Operating System:
      • Win 10
      • Monitor(s):
      • 24" Dell HD + Samsung HD

    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
    Supermarket Generic Brand AETAaAS's Avatar
    Join Date
    May 2013
    Location
    Merseyside
    Posts
    654
    Thanks
    79
    Thanked
    147 times in 129 posts
    • AETAaAS's system
      • Motherboard:
      • MSI B450M Gaming Plus
      • CPU:
      • AMD Ryzen 2600
      • Memory:
      • 16GB Vengeance 3000
      • Storage:
      • Intel 660p 1TB
      • Graphics card(s):
      • EVGA 1080TI SC2
      • PSU:
      • Seasonic Focus 850W
      • Case:
      • Fractal Design Focus G
      • Operating System:
      • Windows 10 x64
      • Monitor(s):
      • HP Envy 32
      • Internet:
      • 17mbps

    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
    RIP Peterb ik9000's Avatar
    Join Date
    Nov 2009
    Posts
    7,701
    Thanks
    1,839
    Thanked
    1,434 times in 1,057 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i7-870, Prolimatech Megahalems, 2x Akasa Apache 120mm
      • Memory:
      • 4x4GB Corsair Vengeance 2133 11-11-11-27
      • Storage:
      • 2x256GB Samsung 840-Pro, 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 U2414h, 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
    Gateshead
    Posts
    15,196
    Thanks
    1,231
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p 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
    RIP Peterb ik9000's Avatar
    Join Date
    Nov 2009
    Posts
    7,701
    Thanks
    1,839
    Thanked
    1,434 times in 1,057 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i7-870, Prolimatech Megahalems, 2x Akasa Apache 120mm
      • Memory:
      • 4x4GB Corsair Vengeance 2133 11-11-11-27
      • Storage:
      • 2x256GB Samsung 840-Pro, 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 U2414h, 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
    Senior Member Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,217
    Thanks
    271
    Thanked
    315 times in 217 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
    Gateshead
    Posts
    15,196
    Thanks
    1,231
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p 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
    RIP Peterb ik9000's Avatar
    Join Date
    Nov 2009
    Posts
    7,701
    Thanks
    1,839
    Thanked
    1,434 times in 1,057 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i7-870, Prolimatech Megahalems, 2x Akasa Apache 120mm
      • Memory:
      • 4x4GB Corsair Vengeance 2133 11-11-11-27
      • Storage:
      • 2x256GB Samsung 840-Pro, 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 U2414h, 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,945
    Thanks
    1,097
    Thanked
    653 times in 482 posts
    • mycarsavw's system
      • Motherboard:
      • P8H77-M Pro
      • CPU:
      • i5 3350P
      • Memory:
      • 16Gb
      • Storage:
      • Lots
      • Graphics card(s):
      • R9 285
      • PSU:
      • HX 620w
      • Case:
      • FD Define Mini
      • Operating System:
      • W10
      • Monitor(s):
      • BenQ G2420HDBL + GL2450HT
      • Internet:
      • Sky

    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)
    |Kata: "Read title as 'fisting'. Not sure why I clicked. Relieved, really."|
    |TAKTAK: "It was so small that mine wouldn't fit into it"|

  21. Received thanks from:

    Tumble (10-05-2017)

  22. #14
    Senior Member Tumble's Avatar
    Join Date
    Jul 2003
    Location
    Right in the Pickle Barrel
    Posts
    7,217
    Thanks
    271
    Thanked
    315 times in 217 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
    RIP Peterb ik9000's Avatar
    Join Date
    Nov 2009
    Posts
    7,701
    Thanks
    1,839
    Thanked
    1,434 times in 1,057 posts
    • ik9000's system
      • Motherboard:
      • Asus P7H55-M/USB3
      • CPU:
      • i7-870, Prolimatech Megahalems, 2x Akasa Apache 120mm
      • Memory:
      • 4x4GB Corsair Vengeance 2133 11-11-11-27
      • Storage:
      • 2x256GB Samsung 840-Pro, 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 U2414h, 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
    2
    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
  •