+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
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. #1

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Smile 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. #2

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Can you post a sample spreadsheet as I am having trouble visualizing what you are trying to do...

  3. #3

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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. #5

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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. #6

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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. #7

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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

  11. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by Matth View Post
    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. #9

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    (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

  13. #10

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    OK now I am thoroughly confused...

    Which column are you trying to get the average of?

  14. #11

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,677
    Thank Post
    3,209
    Thanked 1,030 Times in 955 Posts
    Rep Power
    361
    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. #12

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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]

  17. #13

    Join Date
    Jan 2011
    Posts
    7
    Thank Post
    5
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  18. #14

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    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. #15

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    What he said

  20. Thanks to CESIL from:

    LosOjos (28th January 2011)

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Joomla Blog not showing 2 columns correctly
    By Nick_Parker in forum Web Development
    Replies: 4
    Last Post: 12th August 2010, 05:35 PM
  2. [Pics] Why men should not write advice columns.
    By mattx in forum Jokes/Interweb Things
    Replies: 1
    Last Post: 14th March 2010, 06:14 PM
  3. outlook 2007 calendar add columns
    By imiddleton25 in forum Windows
    Replies: 0
    Last Post: 13th January 2010, 11:54 AM
  4. Layout problem when using 2 columns
    By SimpleSi in forum EduGeek Joomla 1.0 Package
    Replies: 3
    Last Post: 17th May 2008, 09:19 AM
  5. Web query in Excel
    By pinemarten in forum How do you do....it?
    Replies: 2
    Last Post: 20th January 2006, 09:22 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •