I have a table of years and hourly wages in a worksheet:
2012 $10.00
2013 $11.00
2014 $12.00
2015 $13.00
In another worksheet, I want to get the average wage based on a lookup of a starting year and ending year. I've tried a variety of functions using lookup, vlookup, indirect, etc, but can't get it to work.
Please advise.
Thanks
Have you tried the AVERAGEIF() function?
http://www.excelfunctions.net/Excel-...-Function.html
Or the AVERAGEIFS() function?
http://www.excelfunctions.net/Excel-...-Function.html
Last edited by Cutter; 07-01-2012 at 12:49 PM.
averageifs exits in excel 2011 try that
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi MISC_IT,
See the attached with the formula I think you are looking for.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
@MArvin,
That works. Can you please redo the function with the wage table in a different worksheet from the formula? This helps a ton!!
I've tried AVERAGEIF() and AVERAGEIFS(), but cannot make it work. The >= argument for the date returns an error.
AVERAGEIF(A1:A5,and(>=year(c1),<=year(c2),D1:D5)
excel doesn't like the ">" or "<" in these arguments?
Hi,
Here is the formula you want
see attached.
=AVERAGE(INDIRECT("Sheet1!B"&MATCH(D2,Sheet1!$A$2:$A$5,0)+1&":"&"B"&MATCH(E2,Sheet1!$A$2:$A$5,0)+1))
see below for syntax and adjust formula appropriately:
=AVERAGEIFS(B1:B4,A1:A4,">="&"2013",A1:A4,"<="&"2014")=AVERAGEIFS(B1:B4,A1:A4,">="&YEAR(C1),A1:A4,"<="&YEAR(D1))
Last edited by icestationzbra; 07-01-2012 at 01:21 PM.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks