[ms office - 2003] EXCEL Query - Finding the Average if two columns meet a need.

[Matth]

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

[CESIL]

Can you post a sample spreadsheet as I am having trouble visualizing what you are trying to do...

[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

[CESIL]

Try this for example 1

 

=SUMIF(A3:A9,"FAB",B3:B9)/COUNTIF(A3:A9,"FAB")

[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")

[CESIL]

This should work for both examples

 

=IF(COUNTIF($A17:$A23,"FAB")>0,SUMIF($A17:$A23,"FAB",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]

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

[LosOjos]

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...

[Matth]

(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

[CESIL]

OK now I am thoroughly confused...

 

Which column are you trying to get the average of?

[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 ??

[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

 

[edit] and example 2 has three columns of data...[/edit]

[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?

 

Thanks for all your help.

[LosOjos]

OK, you want to use SUMIFS and COUNTIFS, here's your formula:

 

=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:

 

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

[CESIL]

What he said

[Matth]

Hi

 

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

[LosOjos]

Hi

 

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

 

In that case, you're going to have to break it down in to chunks. I haven't got Excel 2003 here, but try this:

 

You'll need to add this formula in to column 'E' in the example, and drag it down so that it calculates on each row:

=IF(IF(A1="FAB",COUNTIF(B1,">0"),0),C1,0)

That will check that each row meets your criteria and if it does, it will put the value of the row in to the cell, if not, it will put 0.

Then, to find your average, use this formula:

=SUM(E1:E6)/COUNTIF(E1:E6,">0")

From the example I shared earlier, your sheet would now look something like this (with the average at the bottom of cell 'E'):

FAB	123	564		564
154	326		0
FAB		985		0
156	125		0
FAB	145	325		325
FAB		125		0
			444.5

[Matth]

Hi

 

Thanks to EVERYONE that contributed - very much appreciated.

The last example and formula work perfectly.

 

Thanks again