Can you post a sample spreadsheet as I am having trouble visualizing what you are trying to do...
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
Can you post a sample spreadsheet as I am having trouble visualizing what you are trying to do...
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
Try this for example 1
=SUMIF(A3:A9,"FAB",B3:B9)/COUNTIF(A3:A9,"FAB")
Matth (27th January 2011)
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")
Matth (27th January 2011)
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
Matth (27th January 2011)
Hi
Thanks for your replies
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
(My reply above was sent before I finished typing it)
Here is my full reply.
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
OK now I am thoroughly confused...
Which column are you trying to get the average of?
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 ??
Matth (28th January 2011)
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
[edit] and example 2 has three columns of data...[/edit]
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?
Thanks for all your help.
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:
then your result would be 444.5Code:FAB 123 564 154 326 FAB 985 156 125 FAB 145 325 FAB 125
Obviously you'll have to play with the ranges in your own spreadsheet to make sure you're picking up all the cells
What he said
LosOjos (28th January 2011)
There are currently 1 users browsing this thread. (0 members and 1 guests)