Office Software Thread, EXCEL Query - Finding the Average if two columns meet a need. in Technical; Hi ALL I'm trying to find the average of a column (let's say column C cells C90:C96) However, column A ...
1. ## 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

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

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

4. Try this for example 1

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

5. ## Thanks to CESIL from:

Matth (27th January 2011)

6. 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")

7. ## Thanks to CESIL from:

Matth (27th January 2011)

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

9. ## Thanks to CESIL from:

Matth (27th January 2011)

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

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

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

13. OK now I am thoroughly confused...

Which column are you trying to get the average of?

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

15. ## Thanks to mac_shinobi from:

Matth (28th January 2011)

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

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

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

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

19. What he said

20. ## Thanks to CESIL from:

LosOjos (28th January 2011)

SHARE:
Page 1 of 2 12 Last