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.

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.

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.

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.

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.

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.

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