Spreadsheet Gurus and Mathy People: Check my work? (Commute Math)

[Future Lazy]

Hi all!

So recently I have been trying to come up with a good system for calculating my True Cost Of Commuting according to the car we own, the repairs it needs and the distance I commute to work. After messing up my formula and math a billion times and having to recalculate it on paper an equal billion times, I realized, DUH I CAN MAKE A SPREADSHEET

But I don't know how to do spreadsheets very good, so here is my first 'final draft':

https://docs.google.com/spreadsheets/d/1jzGt-QxiO5iYiFw5PkoX-UVOnr6pj64zDqDhAJRRNNI/edit?usp=sharing

Everything seems to look really good and the numbers add up, but I'm not completely sure it's solid.

Can all you spreadsheet magicians and mathematical wizards out there take a look and suggest any changes or improvements? Especially if there are corrections to make!

[deborah]

In column K both overtime pays are not multiplied by their loading. Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect. Your yearly calculations are also incorrect, as there are not 52 weeks in a year - that would make a year 364 days, and you do not work 52x5 days per year as there are public holidays and annual leave. Also, you work overtime, so does any involve extra commutes (e.g. working weekends).

[Future Lazy]

In column K both overtime pays are not multiplied by their loading.

At first I was like, whaaat, but then I was like OH DUH

But I'm not 100% sure on how to get them to multiply correctly, since feeding in a formula of "=multiply(H8, H10, 2)" provides the error "Expected two arguments, got three arguments". It must be possible to multiply all three variables within the program in an efficient way, but how?

Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect.

I knew this seemed off, but I'm completely baffled as to how to include that kind of tiered calculation in my spreadsheet. Suggestions or tutorial materials?

Your yearly calculations are also incorrect, as there are not 52 weeks in a year - that would make a year 364 days, and you do not work 52x5 days per year as there are public holidays and annual leave.

So, 365/7 = 52.142857, more accurate?

Not everyone has paid holidays off, or annual leave. Also, not everyone works full time... How could I incorporate these as a variable?

Also, you work overtime, so does any involve extra commutes (e.g. working weekends).

I don't work weekends, so for me it is only 5 days. But it might be a good idea to include a little box that says something like.... "# Days Per Wk Commuting"?


If anybody can point me to a good online class or tutorial that would teach me some of these more complicated bits, that would be just awesome. In the meantime, I suppose I'll just keep messing around with it!

[MDM]

Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect.

I knew this seemed off, but I'm completely baffled as to how to include that kind of tiered calculation in my spreadsheet. Suggestions or tutorial materials?

Feel free to steal the tax calculation in the spreadsheet downloadable from http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228

[solon]

But I'm not 100% sure on how to get them to multiply correctly, since feeding in a formula of "=multiply(H8, H10, 2)" provides the error "Expected two arguments, got three arguments". It must be possible to multiply all three variables within the program in an efficient way, but how?

Instead of "=multiply(H8, H10, 2)" use "=H8*H10*2"

And then forget you ever heard of multiply().

[dandarc]

The formula you want is product - although with 3 numbers, I'd just do X * Y * Z as solon mentions.  Product is cleaner if you've got a range to multiply.

[Future Lazy]

Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect.

I knew this seemed off, but I'm completely baffled as to how to include that kind of tiered calculation in my spreadsheet. Suggestions or tutorial materials?

Feel free to steal the tax calculation in the spreadsheet downloadable from http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228

Thank you thank you!! I think I got it transferred over cleanly, sans the non-payroll bits... But I wouldn't mind having my work checked, if anybody is willing.

But I'm not 100% sure on how to get them to multiply correctly, since feeding in a formula of "=multiply(H8, H10, 2)" provides the error "Expected two arguments, got three arguments". It must be possible to multiply all three variables within the program in an efficient way, but how?

Instead of "=multiply(H8, H10, 2)" use "=H8*H10*2"

And then forget you ever heard of multiply().

Oh my god of course. For some reason I had it in my head that I needed to do something along the lines of "=multiply(A1, B1)divide(C1)" but I just couldn't get it to work. I almost thought I couldn't use multiple operations, which seemed nuts in my head.

Because it was. :)

Thanks a bunch for helping guys, I think I have it going now...