How to find within an array, and return AVG of certain cells

**freerdj** · 05-29-2013, 03:31 PM

Note: Forgive my redundancy, I just posted a thread with the same content, but a different issue, so I figured this should be a new thread.

So I have spreadsheet with 12 monthly tabs of employee performance. For example, the January tab has empID in column A, and "time to complete a certain task" in columns B-AZ. These tasks are grouped (without any empirical identifier) into 10 "parent groups". For example, columns B-C are "Group 1", columns D-J are "Group 2", etc...

Not all employees work each month, so I have a summary tab that contains all possible empIDs in column A, and then 10 columns for each "parent group", for each month (column B-K are for Jan, J-n for Feb, etc...). The expected results for each cell is the AVERAGE of the groups' times, per month. For example, if the empID in A2 is within the Jan array (if they worked in Jan); B2 = AVERAGE of Jan, "Group 1" times; B3 = AVERAGE of Jan, "Group 2" times; et cetera for all 10 "groups", and then repeated for each month.

I was able to achieve this in the summary tab, but only by inserting 10 new columns for each "group", in each month tab, that calculates those averages. I then just did a VLOOKUP for the empIDs and returned values if found: =IFERROR(VLOOKUP($A2,'January'!$C$4:$AW$250,6,FALSE),"-").

Again, I have a solution in place, but it would be nice to not alter the raw data with new columns. With the way the data was initially formatted, is there a way to do a VLOOKUP that returns calculations (IF found, return AVERAGE(this cell:this cell)), instead of just a column reference (like "6" in the formula above)? Or should I abandon VLOOKUP altogether and go another route?

**rcm** · 05-29-2013, 03:35 PM

could you send a sample?

**freerdj** · 05-29-2013, 03:36 PM

I should have specified: I would attach the spreadsheet but it contains sensitive data. I'll fiddle with it for a little bit to see if I can mask those fields without losing the references.

**rcm** · 05-29-2013, 03:44 PM

blank them out!!! names and ids are not relevant as well as headers with company info.

**freerdj** · 05-29-2013, 04:18 PM

Tis attached.

**rcm** · 05-29-2013, 06:01 PM

sorry but I don't see it

**freerdj** · 05-30-2013, 12:34 PM

Example_Stats.xlsx

Attached on OP and here.

**rcm** · 05-31-2013, 08:50 AM

You should look at averageifs()

**freerdj** · 05-31-2013, 09:15 AM

Thanks for the reply. I did look at that, but still not sure that would work; the figures to be averaged are all in the row corresponding to the empID, but they are not adjacent to one another.

If empID is in column A, return average of [this][this][this] or [this:this], in the corresponding row.

**rcm** · 05-31-2013, 09:46 AM

it worked!! the formula is looking for matching columns for the group in the corresponding month tab.

I added columns E and P to form the range name. this can be avoided by adding the formula forming the range name to each cell too.

**rcm** · 05-31-2013, 09:20 AM

the trick is to identify the row first and then average those figures in that row that correspond to the group..

the reason that the id of the corresponding row has to be done first is that averageif only works in one direction at a time. either horizontally or vertically.

i'll try to post your sample with my idea (hopefully working)

**freerdj** · 05-31-2013, 09:22 AM

That would be great, thanks. Yeah I hadn't heard of averageif until earlier this week, so it would really help me understand quicker with pertinent data. Can you do my homework for me?

**freerdj** · 05-31-2013, 09:55 AM

ha whoa. I've got meetings this morning, but when I can I'll spend time stepping through this one, which I'm sure I'll have questions. Thanks so much and schnazzy work.

How to find within an array, and return AVG of certain cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Re: How to find within an array, and return AVG of certain cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions