Matth

Hi Thanks to EVERYONE that contributed - very much appreciated. The last example and formula work perfectly. Thanks again

Hi Thanks for the formula, however, I have Excel 2003 and SUMIFS and COUNTIFS are not supported!

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.

(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

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

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

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