Spread(sheet) 'em

February 22, 2017 11:00 AM

I'm absolutely, positively, completely new at using spreadsheets. I need to keep track of freelance jobs, how much time I'm spending on them, how much each one pays, how much I've been paid in total, and average hourly wage per gig, per freelance site, and overall. Please explain to me how to do these things in Google Sheets in the simplest possible language.

I'm good with setting up the each sheet - one for WebsiteA, one for WebsiteB, etc, headings of each column by freezing row 2, things like that. No problems there. But I have no idea how to set up the formulas I need, and Professor Google is just confusing me more. Here are the situations for which I need formulas:

* In each row, I need to add the numbers in columns F, H, J, L, N, and P, and have the result populated in column Q.

* I need a running total of the sum of all entries in each of column Q and column T to be populated in a lower cell in column Q and T.

* In each row, I need the result of the number in column T divided by the number in column Q, with the answer populated in column V, including the sums that the first two formulas are creating.

* Then I need the Q and T totals, and the calculated answer in V, compiled from sheets 2-9, displayed below the information entered on sheet 1.

I'm even more confused trying to figure out how to explain these things. Maybe if I used actual situations...

Sheet one is the list of all the sites from which I'm generating freelancing income. Fiverr, iWriter, Textbroker, etx. Then each site has its own sheet, where I'm tracking:
> when I submitted a proposal for a gig,
> when it was accepted,
> when and for how long I worked on it - with up to 6 entries per gig,
> total hours worked,
> when I submitted the finished project,
> when the project was approved,
> how much I was paid,
> and what my average "hourly wage" was.

Columns F, H, J, L, N, and P are the entries for how much time I spent working on a gig during each session I worked. (Some projects, I can knock out in one session. Others require more time, and I can't put in the 8, 10, 12 hours all at one shot.) They need added together, to populate column Q.

Column Q is the total hours worked. Somewhere 3-4 cells below the last row entered, I need a running total of how much time I've spent on gigs for this site.

Column T is how much I got paid. Somewhere 3-4 cells below the last row entered, I need a running total of how much money I've earned on gigs for this site.

Column V is my effective hourly wage - so if it took me 15 minutes to do a job I got paid $1 for, my effective hourly wage is $4. Somewhere 3-4 cells below the last row entered, I need the overall average wage, from dividing the total of T by the total of Q.

And then I need each of these numbers conglomerated onto the first sheet, so I can see an overall picture of how much time I'm putting in to freelancing, how much money I'm earning, and my overall hourly wage.

Please help me. My brain hurts.

posted by The Almighty Mommy Goddess to Work & Money (3 answers total)

I started by trying to explain how formulas work, but I think it'll be easier to start from somewhere, so I made a spreadsheet for you. You can copy it into your own drive and play with it from here.

Basic thing on how a formula works: type an equals sign into a cell to indicate you're starting a formula, and then write the formula. You can add things together by saying "=sum(then select the cells you want to sum)". You can divide by typing "=", then clicking on the first cell, typing "/", then the second cell, you're just building a formula.

OK, so my spreadsheet: I don't have all the columns you mention - I don't think you need them all unless you need descriptions for each session or something? But you can add more columns without messing anything up. I did the totals at the top instead of at the bottom.

If you click into any cell you can see if there's a formula there and if so, what it is. If you add new client tabs you can just copy and paste everything, and then you'll need to connect them to the summary tab. The way to do that is to add a new row in the summary tab, then click in the cell where it's "hours worked" should be, type "=", then (without hitting enter) click over to the client's sheet and click on the cell with the total hours worked for the client. Do the same thing for total wages, and then copy the formula for hourly wage. (e.g. click in cell D3, copy, and paste into the new cell).

As long as you don't have more than 1000 projects per client or 8 clients (just realized you said "site" instead of "client", but that's easily editable. it's just the name), you don't have to change anything. If you have more than that you'll need to make sure the rows that sum up include all the rows. If you click on the summary tab and then click on cell B10 (the total hours worked) and then click into the formula bar (where it says "=sum(B2:B8)", you can see an outline of which cell's it's summing. Make sure that box has all your data in it :).

Let me know if you have any questions from here!
posted by brainmouse at 11:29 AM on February 22, 2017 [8 favorites]

Also note you don't have the retype or copy/paste the formulas for every row. Once you have one row set, click to highlight a cell with a formula you want to copy. There will be a little square at the bottom right corner of the cell. Drag that down to copy the same formula to as many rows as you want.
posted by ktkt at 11:36 AM on February 22, 2017

You can make ranges without an upper limit, so they work with more than 1000 rows. Instead of B3:B1000, do B3:B. That will include all of column B, starting at B3

For this reason I like to put my totals and summaries in a frozen row at the top of the spreadsheet, or, if there's a lot of summary information, in a separate page entirely.
posted by aubilenon at 11:40 AM on February 22, 2017 [4 favorites]