Averaging and Complex Counting of Cells Where a Specific Column and Row Header Intersect

**darwin003** · 07-31-2013, 01:24 PM

20pt Commercial Thread Available @ http://www.excelforum.com/showthread.php?t=944704

The original request below is out of date. Thus, I have provided an update here and in the final post:

Thank you all in advance!

Basically I need 4 formulas that do the following to an array of data similar to the one below.

1. Average all data associated with a column that contains the word "pig" in the heading. Hence, this must average even the column below called "Pigcow."
-Potentially solved with array formula: =AVERAGE(TRANSPOSE(IF(ISNUMBER(SEARCH("pig",B2:F2)),IF(LEN(B3:F5)>0,B3:F5))))

2. Do the same thing as the above formula (i.e. only looking at rows that contain "pig" somewhere in the header) and count all values that are 4 and 5 and divide that number by the number of total values (e.g. 1s, 2s, 3s, 4s, & 5s). Hence giving me the percentage of 4s and 5s in the data.
-Potentially solved with array formula: =SUM(IF(ISNUMBER(SEARCH("pig",B2:F2))*(B3:F5>3),1,0))/SUM(IF(ISNUMBER(SEARCH("pig",B2:F2))*(B3:F5>=1),1,0))

3. Calculate an average like in point 1 but this time only average things where columns contain "pig" within the text (same as before) and intersect with rows that have a label equal to "happy." The look up for the rows does not need to search within the text. The full cell contents will always be what I am looking up (e.g. Happy)
-not solved

4. Do the same thing as point 3 above but this time count things like was request in point 2. Thus, count 4s and 5s where the columns containing "pig" intersect with "happy" rows and divide that by the total number of data points (e.g. 1s, 2s, 3s, 4s, & 5s) contained in the same intersecting spots.
-not solved

I have posted in the formula forum if you are curious to see others partial solutions. My request evolved over a couple days and has not been fully solved.
http://www.excelforum.com/excel-form...55#post3351055

Thank you all for your help. I look forward to a solution!

Please note: The above formulas must properly skip blanks written by another formula that returns a value of "".

col1	col2	col3	col4	col5	col6
sad	Pig	Pigcow	Cow	Cow	Pigcow
Happy	1	3	4	2	5
Sad	4	4	3		1
Happy	5	3	4	5

---------------------------------

Hello everyone,

Can anyone help me create a formula that will reference the entire table below but average only the three columns headed by the letter A? It needs to not count blanks as 0.

My full dataset contains many columns and rows and I would love to be able to use formulas to average just those columns headed by A, B, C, D, etc.

A A A B B B
1 1 1 1 1 9
3 2 8 7
2 4 5 8 8
1 5 2 3 2 0
4 1 4 4 8 7
2 3 2 4 5 4
2 2 1 2 0

Thank you in advance for your help.

-Darwin

**JBeaucaire** · 07-31-2013, 01:40 PM

One way:

=SUMPRODUCT((A1:F1="A")*A2:F8) / SUMPRODUCT((A1:F1="A")*(A2:F8<>""))

**darwin003** · 07-31-2013, 02:45 PM

Originally Posted by JBeaucaire

One way:

=SUMPRODUCT((A1:F1="A")*A2:F8) / SUMPRODUCT((A1:F1="A")*(A2:F8<>""))

Thank you so much for the help JBeaucaire!

**JBeaucaire** · 07-31-2013, 05:43 PM

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.

**darwin003** · 08-03-2013, 09:32 PM

Originally Posted by JBeaucaire

I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.

Thank you again JBeaucaire for showing me how to mark a thread as solved. I hope the rep was worth the trouble

This also let me know how to UNsolve my thread and ask another question that builds on the logic you have already helped me with.

Basically what I need to do now is average multiple columns based on TEXT that is contained in the header row. Hence, not just the entire cell contents of the header row.

Thus, in the example below, I would like to be able to average all three rows of data that correspond to columns that contain the string "Pig" (e.g. col 1,2,3, & 6)

Thank you all in advance, this forum truly helps me learn, saves me days of time, and keeps me from getting fired!!

col1	col2	col3	col4	col5	col6
PigCow	Pig	Pigcow	Cow	Cow	Pigcow
1	1	3	4	2	5
2	4	4	3	5	1
3	5	3	4	5	5

**Haseeb Avarakkan** · 08-03-2013, 10:20 PM

Add ISNUMBER(SEARCH("pig" in JB's formula;

=SUMPRODUCT(ISNUMBER(SEARCH("pig",A1:F1))*A2:F8) / SUMPRODUCT(ISNUMBER(SEARCH("pig",A1:F1))*(A2:F8<>""))

**darwin003** · 08-04-2013, 12:34 AM

Originally Posted by Haseeb A

Add ISNUMBER(SEARCH("pig" in JB's formula;

=SUMPRODUCT(ISNUMBER(SEARCH("pig",A1:F1))*A2:F8) / SUMPRODUCT(ISNUMBER(SEARCH("pig",A1:F1))*(A2:F8<>""))

Thanks man.

I was using search but didn't have the isnumber part.

I still don't really understand what that is really doing in the formula but at least this solves my problems!

**darwin003** · 08-04-2013, 01:49 AM

Sigh... it worked on my test data but not on the real data.

I isolated the problem to the fact that blank cells in my data are generated by an hlookup formula that reads a previous formula that turns 0's into blanks via something like if(A1=0,"",A1).

For some reason this screws up the formula that Haseeb was kind enough to share with me.

Deleting the blank cells manually makes the formula work perfectly but that isn't an option I can use.

Any idea how to fix this excel gods?

**RobertMika** · 08-04-2013, 06:34 AM

Try this array formula(confirm Control+Shift+Enter)
=AVERAGE(TRANSPOSE(IF(ISNUMBER(SEARCH("pig",A1:F1)),IF(LEN(A2:F8)>0,A2:F8))))

**darwin003** · 08-04-2013, 11:23 AM

Originally Posted by RobertMika

Try this array formula(confirm Control+Shift+Enter)
=AVERAGE(TRANSPOSE(IF(ISNUMBER(SEARCH("pig",A1:F1)),IF(LEN(A2:F8)>0,A2:F8))))

This works wonderfully on my test data and real data. Thank you so much

So now I have two final question! Sorry for not asking these all at once, I didn't see them coming.

How can I take RobertMika's great formula above and have it only average data in columns that contain the string "pig" as before, but now only where those "pig" columns intersect with "happy" rows. The row lookup part should only look for the complete cell to be named "happy." It does not need to look within the string like the column part.

Then, how can I modify this formula (no longer averaging anything) to count the number of 4s and 5s contained in the chosen intersecting cells and divide the result by the number of total numbers (e.g. 1s,2s,3s,4s,&5s). Hence, giving me the percentage of 4s and 5s. -I got this working using RobertMika's formula for just the columns and an overall count but am having a hard time making it work for where row's intersect columns and for counting only cells with a specific number.

I thought I could just change AVERAGE to COUNT in the formula below and change the bold par of the formula below to "=1" but that didn't work. Interestingly, I can remove that bold part from the formula and it doesn't seem to impact it working at all.

=AVERAGE(TRANSPOSE(IF(ISNUMBER(SEARCH("pig",A1:F1)),IF(LEN(A2:F8)>0,A2:F8))))[/QUOTE]

Both again need to skip blanks.

Thank you again everyone for the help and especially for those who are replying! I know how to use these formulas in pieces but I'm still trying to wrap my head around getting them to all work in conjunction. Hence, my plea for help

col1	col2	col3	col4	col5	col6
sad	Pig	Pigcow	Cow	Cow	Pigcow
Happy	1	3	4	2	5
Sad	4	4	3		1
Happy	5	3	4	5

**darwin003** · 08-04-2013, 01:53 PM

20pt Commercial Thread Available @ http://www.excelforum.com/showthread.php?t=944704

I tried to clean things up a bit for anyone seeking information about what I am attempting to do and have solved so far with the help of the amazing

Thank you all in advance!

Basically I need 4 formulas that do the following to an array of data similar to the one below.

1. Average all data associated with a column that contains the word "pig" in the heading. Hence, this must average even the column below called "Pigcow."
-Potentially solved with array formula: =AVERAGE(TRANSPOSE(IF(ISNUMBER(SEARCH("pig",B2:F2)),IF(LEN(B3:F5)>0,B3:F5))))

2. Do the same thing as the above formula (i.e. only looking at rows that contain "pig" somewhere in the header) and count all values that are 4 and 5 and divide that number by the number of total values (e.g. 1s, 2s, 3s, 4s, & 5s). Hence giving me the percentage of 4s and 5s in the data.
-Potentially solved with array formula: =SUM(IF(ISNUMBER(SEARCH("pig",B2:F2))*(B3:F5>3),1,0))/SUM(IF(ISNUMBER(SEARCH("pig",B2:F2))*(B3:F5>=1),1,0))

3. Calculate an average like in point 1 but this time only average things where columns contain "pig" within the text (same as before) and intersect with rows that have a label equal to "happy." The look up for the rows does not need to search within the text. The full cell contents will always be what I am looking up (e.g. Happy)
-not solved

4. Do the same thing as point 3 above but this time count things like was request in point 2. Thus, count 4s and 5s where the columns containing "pig" intersect with "happy" rows and divide that by the total number of data points (e.g. 1s, 2s, 3s, 4s, & 5s) contained in the same intersecting spots.
-not solved

I have posted in the formula forum if you are curious to see others partial solutions. My request evolved over a couple days and has not been fully solved.
http://www.excelforum.com/excel-form...55#post3351055

Thank you all for your help. I look forward to a solution!

Please note: The above formulas must properly skip blanks written by another formula that returns a value of "".

col1	col2	col3	col4	col5	col6
sad	Pig	Pigcow	Cow	Cow	Pigcow
Happy	1	3	4	2	5
Sad	4	4	3		1
Happy	5	3	4	5

**vlady** · 08-04-2013, 09:18 PM

i think you should just add another validation for "happy"

=AVERAGE(TRANSPOSE(IF(ISNUMBER(SEARCH("pig",B2:F2))*(A3:A5="happy"),IF(LEN(B3:F5)>0,B3:F5)))) -same goes with the #4 formula

**RobertMika** · 08-05-2013, 12:10 AM

No really need for TRANSPOSE
=AVERAGE(IF(A2:A7="Happy",IF(ISNUMBER(SEARCH("pig",B1:F1)),IF(LEN(B2:F7)>0,B2:F7))))
Confirm Control+Shift+Enter

or
=AVERAGE(IF(A2:A7="Happy",IF(ISNUMBER(SEARCH("pig",B1:F1)),IF(NOT(ISBLANK(B2:F7)),B2:F7))))

For the pigcow/happy

For pig/happy only
=AVERAGE(IF(A2:A7="Happy",IF(LEN(B1:F1)=LEN("pig"),IF(ISNUMBER(SEARCH("pig",B1:F1)),IF(LEN(B2:F7)>0,B2:F7)))))

Only moderators and admins have access to comercial section.

**JBeaucaire** · 08-05-2013, 01:34 AM

...Only moderators and admins have access to comercial section.

And forum Gurus. The note he added was at my behest, normally duplicate posts are frowned up, but in this instance it's more of a cross-post, so the link is intended to comply with forum rules regarding cross-posting.

**arlu1201** · 08-05-2013, 04:36 AM

Thread posted in Commercial services subforum.

This thread is closed.

Averaging and Complex Counting of Cells Where a Specific Column and Row Header Intersect

LinkBack

Thread Tools

Display

Averaging and Complex Counting of Cells Where a Specific Column and Row Header Intersect

Re: Averaging Multiple Columns Based on Header Row

Re: Averaging Multiple Columns Based on Header Row

Re: Averaging Multiple Columns Based on Header Row

Re: Averaging Multiple Columns Based on Header Row

Re: Averaging Multiple Columns Based on Partial String of Text Contained in Header Row

Re: Averaging Multiple Columns Based on Partial String of Text Contained in Header Row

Re: Averaging Multiple Columns Based on Partial String of Text Contained in Header Row

Re: Averaging Multiple Columns Based on Partial String of Text Contained in Header Row

Re: Averaging Multiple Columns Based on Partial String of Text Contained in Header Row

Re: Averaging Multiple Columns Based on Partial String of Text Contained in Header Row

Re: Averaging and Complex Counting of Cells Where a Specific Column and Row Header Interse

Re: Averaging and Complex Counting of Cells Where a Specific Column and Row Header Interse

Re: Averaging and Complex Counting of Cells Where a Specific Column and Row Header Interse

Re: Averaging and Complex Counting of Cells Where a Specific Column and Row Header Interse

Thread Information

Users Browsing this Thread

Similar Threads

Formula help (averaging multiple columns)

[SOLVED] Sort multiple columns based on column header

Count repeated value(text) in Data range based on row header & multiple columns header

Sum of multiple columns based on text contents of a header cell

Excel 2007 : Averaging multiple columns row by row

Bookmarks

Bookmarks

Posting Permissions