Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Averaging values in column A when certain criteria are met in colu

I have values in column Y, and a number of different names in column C. I
would like to create an average of the Y values for a select group of the
names from C. I have tried the following two ways and get a #DIV/0! error
both times. Any help is appreciated!

{=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))}

{=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))}

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Averaging values in column A when certain criteria are met in colu

Try

(with blank entries in Y for mathcing records)
=AVERAGE(IF($C$2:$C$60000={"Jane","John","Joan","J ean"},$Y$2:$Y$60000))

(excluding blanks)
=AVERAGE(IF(($C$2:$C$60000={"Jane","John","Joan"," Jean"})*($I$2:$I$60000<""),$Y$2:$Y$60000))

If this post helps click Yes
---------------
Jacob Skaria


"VichyB" wrote:

I have values in column Y, and a number of different names in column C. I
would like to create an average of the Y values for a select group of the
names from C. I have tried the following two ways and get a #DIV/0! error
both times. Any help is appreciated!

{=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))}

{=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))}

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Averaging values in column A when certain criteria are met in colu

Please note that these are array formulas. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"VichyB" wrote:

I have values in column Y, and a number of different names in column C. I
would like to create an average of the Y values for a select group of the
names from C. I have tried the following two ways and get a #DIV/0! error
both times. Any help is appreciated!

{=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))}

{=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))}

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Averaging values in column A when certain criteria are met in

that did it! thanks so much.

"Jacob Skaria" wrote:

Try

(with blank entries in Y for mathcing records)
=AVERAGE(IF($C$2:$C$60000={"Jane","John","Joan","J ean"},$Y$2:$Y$60000))

(excluding blanks)
=AVERAGE(IF(($C$2:$C$60000={"Jane","John","Joan"," Jean"})*($I$2:$I$60000<""),$Y$2:$Y$60000))

If this post helps click Yes
---------------
Jacob Skaria


"VichyB" wrote:

I have values in column Y, and a number of different names in column C. I
would like to create an average of the Y values for a select group of the
names from C. I have tried the following two ways and get a #DIV/0! error
both times. Any help is appreciated!

{=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000 ="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Je an"),$Y$2:$Y$60000))}

{=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$6000 0="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$6000 0="Jean",(IF($Y$2:$Y$60000))))))))))}

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count how many criteria in a column match criteria in another colu Charles Stover Excel Discussion (Misc queries) 3 March 6th 09 09:39 PM
Sum of items in a column if they meet two criteria in another colu vlpckett Excel Worksheet Functions 6 February 13th 09 12:44 AM
i wish to lookup values in column A, & add adjacent values in colu Browny Excel Discussion (Misc queries) 4 September 26th 08 05:00 PM
HELP: Averaging values if they meet certain criteria KellyF Excel Worksheet Functions 3 October 19th 07 02:15 PM
Advanced Filter for Values in Column M greater than Values in Colu SteveC Excel Discussion (Misc queries) 3 May 2nd 06 07:55 PM


All times are GMT +1. The time now is 07:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"