0
$\begingroup$

I'm trying to take the money earned over several days - each with unique hours worked and dollars earned per hour (hrs and $/hr). What I've done is multiply each days' # of hours by its rate to get the money earned for each day. From there, I totaled the money earned, and found the percentage for the money earned each day over the total.

With this information, I'm trying to find out how I can find the average rate of pay, weighting the different days based on their rates and hours.

I tried finding the weight for each day by multiplying each days rate by its percentage earned of the total money. With these values I added them up, and then divided by the total amount of days. This seems wildly off. I then tried to simply add up the weighted values. This gets the correct value (total money/total hours), but only when all of the rates are the same. How can I set up an equation to find the weights of each and use these weights to find the average rate of pay?

I've got this setup in excel, so if I need to paste in the relevant parts of the document I can.

$\endgroup$
2
  • 1
    $\begingroup$ The simplest approach is that the average rate is total money earned divided by total hours worked. Why make it more complicated? $\endgroup$ – Ross Millikan Jan 31 '16 at 1:59
  • $\begingroup$ I really like math, so sometimes I will try and find alternate routes to an answer. I got stuck before I figured the total money/total hours method, and I'm very interested how I would solve it with my original method $\endgroup$ – OSG Jan 31 '16 at 2:13
0
$\begingroup$

Let's say there are $n$ days, for which $h_i$ is the number of hours worked and $r_i$ is the number of dollars per hour for that day for $i \in \Bbb{N}$. $h_i$ is the weight on each day because days with more hours affect the average rate of pay more. Therefore, to find the weighted sum, we simply need to sum up all of the $r_i$s with a weight of $h_i$, which can be expressed as: $$\sum_{i=1}^n r_ih_i$$ Then, to find the weighted average, we need to divide this weighted sum by the total number of hours. The total number of hours is the sum of all $h_i$, or: $$\sum_{i=1}^n h_i$$ Thus, we just need to divide the first part by the second part: $$\frac{\sum_{i=1}^n r_ih_i}{\sum_{i=1}^n h_i}$$ Notice that this is exactly the same as total money divided by total hours. However, we are just looking at this process differently by looking at the $r_i$s as our objects and the $h_i$s as our weights.

$\endgroup$
5
  • $\begingroup$ I've got this all worked out in excel, but the calculated rate is always coming out to four times the actual average rate. I can't figure out what is going wrong. Is there a way I can share my excel file? I could just divide the amount by four, but I'd like to correct it at the root and find out why its wrong. $\endgroup$ – OSG Jan 31 '16 at 2:25
  • $\begingroup$ Hmm, the average hours per day is 4, perhaps that's what should be divided out? $\endgroup$ – OSG Jan 31 '16 at 2:31
  • $\begingroup$ @OSG Is there anyway you could upload it to Google Drive and then make the file public? Then, you could put the link here and everyone would be able to view it. $\endgroup$ – Noble Mushtak Jan 31 '16 at 13:24
  • $\begingroup$ I'm pretty sure I've solved this little issue, though each time I look at it I'm more thoroughly convinced I'm just over-complicating it. Here is the Google Drive spreadsheet. I tried to write down an theoretical explanation but I'm a little tired and can't think through it quite right. $\endgroup$ – OSG Feb 1 '16 at 4:18
  • $\begingroup$ @OSG I'm sorry; I thought you were trying to find the average rate per day. I've edited my answer to find the average rate per hour above. In your spreadsheet, I think that this would just be SUMPRODUCT(A:A, B:B)/SUM(A:A), although the way you are doing it right now is mathematically equivalent to that. $\endgroup$ – Noble Mushtak Feb 1 '16 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.