# EXCEL Query - Finding the Average if two columns meet a need.

Show 25 post(s) from this thread on one page
Page 1 of 2 12 Last
• 27th January 2011, 10:18 AM
Matth
EXCEL Query - Finding the Average if two columns meet a need.
Hi ALL

I'm trying to find the average of a column (let's say column C cells C90:C96)

However, column A needs to contain a text value (let's say "FAB" A90:A96)

AND

Column B needs to contain a numeric value (for each occurance of "FAB").

My current formula doesn't sort out the column B issue and if a column doesn't contain FAB it displays a #DIV/0!

=AVERAGE(IF(A90:A96="FSM",C90:C96,"0"))

Any help would be greatly appreciated.

Thanks :D
• 27th January 2011, 11:18 AM
CESIL
Can you post a sample spreadsheet as I am having trouble visualizing what you are trying to do...
• 27th January 2011, 12:02 PM
Matth
Hi

Thanks for posing a question.

Below are two examples - I'm using Excel 2003

Example 1
FAB 2009 2010 Progress
FAB 4 6 2
6 6 0
FAB 5 5 0
FAB 7 9 2
5 6 1
FAB 3 3
5 5 0
Overall Average 1.14
Average of FAB -

Example 2

FAB 2008 2009 2010 Progress
2 3 5 2
3 4 4 0
4 5 5 0
3 4 5 1
4 4 6 2
3 3 4 1
4 3 3 0
Overall Average 0.86
Average of FAB -

I only want to know the average of those who have FAB and have a score in the two most recent columns - 2009 and 2010 column (EXAMPLE 1).
AND

how not to get a #DIV/0! error when FAB is not relevant to the table (EXAMPLE 2).

I hope the above is of help.
Thanks
• 27th January 2011, 12:46 PM
CESIL
Try this for example 1

=SUMIF(A3:A9,"FAB",B3:B9)/COUNTIF(A3:A9,"FAB")
• 27th January 2011, 12:49 PM
CESIL
Use this version if you want to copy into other columns

=SUMIF(\$A\$3:\$A\$9,"FAB",C3:C9)/COUNTIF(\$A\$3:\$A\$9,"FAB")
• 27th January 2011, 01:01 PM
CESIL
This should work for both examples

=IF(COUNTIF(\$A17:\$A23,"FAB")>0,SUMIF(\$A17:\$A23,"FA B",B17:B23)/COUNTIF(\$A17:\$A23,"FAB"),0)

The red part should change to match the column you copy to...or you could probably use a function to get the column and row automatically
• 27th January 2011, 02:19 PM
Matth
Hi

The third reply meets my needs the most (removes the #DIV/0! message).

However, it doesn't contain a key item.

I can only have the average of "FAB" if they have recorded results in the last two years.

=IF(COUNTIF(\$A17:\$A23,"FAB")>0,SUMIF(\$A17:\$A23,"FA B",B17:B23)/COUNTIF(\$A17:\$A23,"FAB"),0)

column A

anything that allows 2009 and 2010 reuslts to be works the best
• 27th January 2011, 02:25 PM
LosOjos
Quote:

Originally Posted by Matth
anything that allows 2009 and 2010 reuslts to be works the best

having a bit of trouble understanding you here: the forula works on whichever columns you tell it to, just don't include earlier columns if you don't want to...
• 27th January 2011, 02:26 PM
Matth
(My reply above was sent before I finished typing it)

Hi

Thanks for your time and replies.

The third reply meets my needs the most (removes the #DIV/0! message).

I don't understand the red part of your formula.

Also I can only have the average of "FAB" if they have recorded results in the last two years.

=IF(COUNTIF(\$A17:\$A23,"FAB")>0,SUMIF(\$A17:\$A23,"FA B",B17:B23/COUNTIF(\$A17:\$A23,"FAB"),0)

Column A Contains FAB
Column B Contains 2009 results
column C contains 2010 results

Thanks again
• 27th January 2011, 04:43 PM
CESIL
OK now I am thoroughly confused...

Which column are you trying to get the average of?
• 27th January 2011, 05:17 PM
mac_shinobi
From what I can gather it sounds like

If the value or text in column A is FAB then check to ensure columns B and C are both numeric ( if so then average columns B and C )

Obviously if column A does not contain FAB then ignore it completely

Otherwise I am totally lost with what the OP is trying to achieve ??
• 27th January 2011, 11:36 PM
CESIL
An average for B and a separate average for C?

I am confused because in example 2 no column has FAB in it...or am I still missing something :confused:

 and example 2 has three columns of data...[/edit]
• 28th January 2011, 09:06 AM
Matth
Hi All

I apologise for any confusion surrounding my question. I’ll ensure future questions are laid out clearer!

Both of my examples lost their formatting between typing and displaying on the web.

Example 2 was displaying the fact that I had numeric data in columns B and C but no “FAB” in column A. This scenario results in my computer displaying the #DIV/0! error.

Example layout of table -
Column A – Contains FAB (or not)
Column B – Contains 2009 data
Column C – Contains 2010 data
Column D – Contains the difference between Column C and Column B

Mac_shinobi was the closest at guessing what my request was.

So here goes (copying some text that mac_shinobi typed).

If the value or text in column A is FAB then check to ensure a corresponding row in column B is numeric. If this figure / row is numeric then find out the average of all corresponding numbers in column C.
In reverse – average of column C only if column B has a numeric value and column A contains “FAB”.

Obviously if column A does not contain FAB then ignore it completely

Does this explanation help?

• 28th January 2011, 09:37 AM
LosOjos
OK, you want to use SUMIFS and COUNTIFS, here's your formula:

Code:

`=SUMIFS(C1:C6,A1:A6,"FAB",B1:B6,">0")/COUNTIFS(A1:A6,"FAB",B1:B6,">0")`

So if you had the following table for instance:

Code:

```FAB      123    564         154    326 FAB            985         156    125 FAB      145    325 FAB            125```
then your result would be 444.5

Obviously you'll have to play with the ranges in your own spreadsheet to make sure you're picking up all the cells
• 28th January 2011, 09:46 AM
CESIL
What he said :)
Show 25 post(s) from this thread on one page
Page 1 of 2 12 Last