Hi, new poster here! So, my boss asked me to build an array formula that computes the average hourly wage by month. I copied a few cells and columns and pasted them below to give you an idea. I have no idea how to do this...please help!
Hrs Amt
Jack Apr-11 2,000 110,000
Bob Apr-11 14,000 1,050,000
Jill Jan-11 7,540 452,400
Harry Jan-11 7,449 670,410
Larry Apr-11 2,300 138,000
TommyJan-11 6,877 550,160
Craig Apr-11 9,000 675,000
Bryan Apr-11 11,987 479,480
Hi MPCPA,
See the attached file and let me know if this works for you. thanks.
Regards,
DILPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
Hi DILPandey!
Actually he doesn't want me to create any new columns or rows. Just an array formula that will put the average in one cell. Do you, or anyone else, know how to do this?
Thanks!
Ok.. In that case you can combine the formula of H, I, J .. also is he open to mention month ? Else it would be very difficult to assume which month's avg wages to be calculated
Regards,
DILIPandey
<click on below 'star' if this helps>
Is that an array formula? I think what he wants is an array formula that does a look up to see if the month is, say January, and then average D into C. Does that make sense? This is a tough one!
I'm not sure if you need an array, dilipandey showed you can just use SUMIF functions, attached is an all in one formula.
Happy Excel'ing!
Thanks, but that is not an average hourly wage rate. That is just taking the two totals and dividing them. I think I need a vlookup that will find the month and then divides the hours by the amount for each and then averages it. Make sense?
Wouldn't it be easier to make another column with average hourly wage rate (monthly salary/hours), then just AVERAGEIF the column by month? AVERAGEIF is very similar to SUMIF.
But I think I know what you want to do, you want a function that will check the table by month, then divide each salary by hours, then average all the hourly rates. Maybe a Guru can take that on.
Working from DILIPandey's workbook, does this do what you want?
=SUMPRODUCT(--(MONTH($B$2:$B$9)=4),$D$2:$D$9/$C$2:$C$9)/SUMPRODUCT(--(MONTH(B2:B9)=4))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Yes, it would be easier! And you are correct. The problem is my boss said NO more columns or rows. I can't figure out how to do it, I am hoping an excel guru here knows how to do it. Come on excel gurus!
So does that work? In my example, I calculated for April (month 4)
I disagree. Totalling the total paid for the month and dividing that total by the total hours worked in the month gives exactly what you asked for, an average hourly rate for that month. If you do what you suggest then the result can be seriously skewed by one individual who works a small number of hours at a low rate, for example, e.g. what if one worker worked 1 hour for 1 dollar and another worked 500 hours for 5000 dollars? your proposal would give an average rate of $5.5 when the rate is nearly $10Originally Posted by MFCPA
Audere est facere
@ChemistB, I think that works, thanks!
@daddylonglegs, he is asking for the average hourly rate. Not the average rate.
Another question. What if I want to total the hours per day in the entire year? Is there an excel formula for this? Thanks for all the help, I'm trying to get better at excel!
OK, I'll try one more time - If you have 8 employees who between them earn $1000 in March 2012 while working 100 hours between them then the average hourly rate for your employees for March is simply 1000/100 = $10. The actual distribution of those hours shouldn't really matter.
If you use the other method then you will be giving equal "weight" to one employee working 1 hour as to another working 100 hours so what you will get is an average of the hourly rate for each employee, effectively averaging an average, which isn't usually a good idea.
Of course you can use whatever method you want but I personally I would use the SUMIF/SUMIF method proposed by Dilipandey.
@daddylonglegs, I agree that it isn't giving the best "picture" but that is what he is asking for, the average hourly rate. So I need to take the rate per each person and average it. The amount of hours they work is irrelevant.
How about what if I want to total the hours per day in the entire year? Any ideas? Thanks!
If you want the total hours for 2011 try this formula
=SUMPRODUCT((YEAR(B2:B9)=2011)+0,C2:C9)
That gives me the total hours for 2011, but how do I count the number of days in 2011 (assuming everyday is a working day) and divide hours into it?
Do you mean just dividing by the total number of days in 2011? as that will be 365 then the average hours per day will be
=SUMPRODUCT((YEAR(B2:B9)=2011)+0,C2:C9)/365
....or am I misunderstanding? If so then can you give me a small example?
Thank you! That works! I'm very good at math, but I suck at excel. I'm trying to learn more. You obviously know what you are doing.
One more quick question here then I promise no more! If I have the person, hours, and need the wages (see attachment), how do I do that? I guess I need to do a vlookup, figure the hourly wage, and then multiply it by the hours?
Thanks again for looking at this, you really need to know excel to do this!
Try this formula in I3 copied down
=H3*VLOOKUP(G3,A$2:D$9,4,0)/VLOOKUP(G3,A$2:D$9,3,0)
Note - I got #N/A in I6 because "Tommmy" is misspelt in G6.....
You are the man (woman) daddylonglegs! Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks