Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 64
Default AVERAGE IF with multiple conditions

I have 5 columns of data and need a a formula that will allow me to calculate
the following:

Average the values of column E for all instances where column A = "P1" and
column B = "Red" and Column D = "20". I.e. the average will only be
calculated for the lines in column E where the above conditions have been met
in the other columns, as mentioned.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default AVERAGE IF with multiple conditions

This expression should get you there
In say, F2, array-entered,
ie confirm the formula by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF((A2:A10="P1")*(B2:B10="Red")*(D2:D10=2 0),E2:E10))
Adjust the ranges to suit

Above assumes that col D contains real nums.
If its text nums, change it to: D2:D10="20"
(put double quotes around the 20)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"WildWill" wrote:
I have 5 columns of data and need a a formula that will allow me to calculate
the following:

Average the values of column E for all instances where column A = "P1" and
column B = "Red" and Column D = "20". I.e. the average will only be
calculated for the lines in column E where the above conditions have been met
in the other columns, as mentioned.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default AVERAGE IF with multiple conditions

Try this array formula** :

=AVERAGE(IF((A1:A10="P1")*(B1:B10="red")*(D1:D10=2 0),E1:E10))

Better to use cells to hold the criteria:

G1 = P1
H1 = red
I1 = 20

=AVERAGE(IF((A1:A10=G1)*(B1:B10=H1)*(D1:D10=I1),E1 :E10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"WildWill" wrote in message
...
I have 5 columns of data and need a a formula that will allow me to
calculate
the following:

Average the values of column E for all instances where column A = "P1" and
column B = "Red" and Column D = "20". I.e. the average will only be
calculated for the lines in column E where the above conditions have been
met
in the other columns, as mentioned.



  #4   Report Post  
Posted to microsoft.public.excel.misc
TGV TGV is offline
external usenet poster
 
Posts: 63
Default AVERAGE IF with multiple conditions

Thanks for your suggestions, but both of the answers are not resolved my
issue. Please go through my post once again I want to highlight each rows
maximum value. Anyone pls help me.

Thank you,

TGV


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A1:A10="P1")*(B1:B10="red")*(D1:D10=2 0),E1:E10))

Better to use cells to hold the criteria:

G1 = P1
H1 = red
I1 = 20

=AVERAGE(IF((A1:A10=G1)*(B1:B10=H1)*(D1:D10=I1),E1 :E10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"WildWill" wrote in message
...
I have 5 columns of data and need a a formula that will allow me to
calculate
the following:

Average the values of column E for all instances where column A = "P1" and
column B = "Red" and Column D = "20". I.e. the average will only be
calculated for the lines in column E where the above conditions have been
met
in the other columns, as mentioned.




  #5   Report Post  
Posted to microsoft.public.excel.misc
TGV TGV is offline
external usenet poster
 
Posts: 63
Default AVERAGE IF with multiple conditions

Thanks for your suggestions, but both of the answers are not resolved my
issue. Please go through my post once again I want to highlight each rows
maximum value. Anyone pls help me.

Thank you,

TGV


"Max" wrote:

This expression should get you there
In say, F2, array-entered,
ie confirm the formula by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF((A2:A10="P1")*(B2:B10="Red")*(D2:D10=2 0),E2:E10))
Adjust the ranges to suit

Above assumes that col D contains real nums.
If its text nums, change it to: D2:D10="20"
(put double quotes around the 20)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"WildWill" wrote:
I have 5 columns of data and need a a formula that will allow me to calculate
the following:

Average the values of column E for all instances where column A = "P1" and
column B = "Red" and Column D = "20". I.e. the average will only be
calculated for the lines in column E where the above conditions have been met
in the other columns, as mentioned.



  #6   Report Post  
Posted to microsoft.public.excel.misc
TGV TGV is offline
external usenet poster
 
Posts: 63
Default AVERAGE IF with multiple conditions

Please ignore my previous posts in this topic and sorry for the
inconvenience. I thought to send it to my above query €śConditional
formatting help needed €ś but by mistake I have posted it in this topic.

TGV


"TGV" wrote:

Thanks for your suggestions, but both of the answers are not resolved my
issue. Please go through my post once again I want to highlight each rows
maximum value. Anyone pls help me.

Thank you,

TGV


"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF((A1:A10="P1")*(B1:B10="red")*(D1:D10=2 0),E1:E10))

Better to use cells to hold the criteria:

G1 = P1
H1 = red
I1 = 20

=AVERAGE(IF((A1:A10=G1)*(B1:B10=H1)*(D1:D10=I1),E1 :E10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"WildWill" wrote in message
...
I have 5 columns of data and need a a formula that will allow me to
calculate
the following:

Average the values of column E for all instances where column A = "P1" and
column B = "Red" and Column D = "20". I.e. the average will only be
calculated for the lines in column E where the above conditions have been
met
in the other columns, as mentioned.




  #7   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default AVERAGE IF with multiple conditions

I've just responded further to you in your thread. Your data may not be all
real nums. Check it out over there. Pl mark all responses which help you in
any/some way by clicking the YES button (like the ones below).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

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
Average If with multiple conditions WildWill Excel Discussion (Misc queries) 1 February 10th 09 11:45 AM
Average of the last x entries with conditions Gilbert DE CEULAER Excel Worksheet Functions 3 September 28th 08 08:13 PM
Average with multiple conditions Dez Excel Worksheet Functions 4 July 11th 07 06:07 PM
AVERAGE with conditions mr_concrete Excel Worksheet Functions 3 February 7th 07 09:23 PM
average on 2 conditions Ted Metro Excel Worksheet Functions 6 January 7th 05 09:23 PM


All times are GMT +1. The time now is 09:41 PM.

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"