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:
https://s5.postimg.org/uas21413b/excelproblem.jpg

Any suggestions gratefully received :)

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).

http://i.imgur.com/OAJiNUT.png

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.

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...

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.
https://s5.postimg.org/cyrpfo7lz/mileage_sheet.jpg

Access isn't an option unfortunately, as I don't have it/can't install it on my computer.

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)

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

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

Quote:

---

Originally Posted by MaddAussie

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

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...

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

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

Quote:

---

Originally Posted by ik9000

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... :embarrassed: :confused:

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

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

https://s16.postimg.org/91o6p7ejp/mileage_totals.png

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)

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 :p Your solution is a lot more elegant! Question though: What does the $ do?

Quote:

---

Originally Posted by Tumble

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 :p 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

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

Thanks for all the help & suggestions :) I believe I have got it gripped now, and in the layout I've been using :) Can always count on Hexus for an assist!

Quote:

---

Originally Posted by MalcolmMurdoch

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

---

because it thinks you're a prune?

Quote:

---

Originally Posted by MalcolmMurdoch

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

---

Quote:

---

Originally Posted by ik9000

because it thinks you're a prune?

---

HAHAH.. no, really I know this one! That'll be your basic cell formatting - you need to change it to either "number" or "general"