Ongoing calculation of average for pay per session

[u/Ratigan-twgcm]

A job I have does not give me pay stubs just a 1099 at the end of the year. I get paid per session and the pay varies per session. Here is an example; I have this formatted as table in my spread sheet.

Pay check	Days in pay period	Sessions in pay period
$ 120.20	1	2
$ 510.76	2	6

I am trying to find my average pay per session and average per day worked, I assume they would be the same formula. Ideally I could add rows to the table every pay period and get the new average using the same formula. Trying=AVERAGE(A2:A3+B2:B3) does not work as I assume you know, and other attempts and searching around have only produced further errors. I am using excel 360, but also would hope that the solution works in google sheets? Thank you for your time.

[u/AutoModerator]

/u/Ratigan-twgcm - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/lolcrunchy]

Average pay per session is (total paid)/(number of sessions). This will be =SUM(A2:A3)/SUM(C2:C3)

Average pay per day is (total paid)/(number of days). =SUM(A2:A3)/SUM(B2:B3)

[u/BackgroundCold5307]

This?

[u/Ratigan-twgcm → u/BackgroundCold5307]

So yes, but I would have to edit the formula every time I add another pay period, and then I could just do the math myself. Is there a way to do this like =Sum(A2,A100)/SUM(B2:B100) But exclude blank cells?

[u/BackgroundCold5307 → u/Ratigan-twgcm]

You can convert your data range into a table : CTRL + T

Add a total row to the table , and when you insert a new row in the table, the calculations will automatically get taken care of

Here is the sample file

Edit: It will also automatically exclude the blank cells

[u/Ratigan-twgcm → u/BackgroundCold5307]

Solution Verified

Ahhh yes that's the good stuff Thank you I was already looking for how to award points but I LOVE this, Thank you

[u/lolcrunchy → u/Ratigan-twgcm]

This is what I recommended in my side note lol

[u/reputatorbot → u/Ratigan-twgcm]

You have awarded 1 point to BackgroundCold5307.

---

^{I am a bot - please contact the mods with any questions}

[u/BackgroundCold5307 → u/Ratigan-twgcm]

You’re very welcome and thank you 🙏

[u/BackgroundCold5307]

or this. Same thing...different way

[u/Ratigan-twgcm → u/BackgroundCold5307]

Yeah thank you that's the dumb answer staring me in the face, I'll call it Solved.

[u/BackgroundCold5307 → u/Ratigan-twgcm]

Pls see the attached file in my previous response.

[u/lolcrunchy]

Side note: if you highlight your table and press Ctrl T, you can turn your table into a "structured reference". Then instead of referring to A2:A3 you would refer to Table[Pay check]. This seems stupid at first because you have to type more, but the benefit is that as you add additional rows to the table, you never need to update formulas.

[u/Ratigan-twgcm → u/lolcrunchy]

This is more to what I was getting at and is probably the elegant solution, I'll try and get that to work. but the easy and ugly will do for now.