AVERAGE IF with multiple conditions

[WildWill]

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.

 

[Max]

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")*(D210=20),E2:E10))
Adjust the ranges to suit

Above assumes that col D contains real nums.
If its text nums, change it to: D210="20"
(put double quotes around the 20)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF((A1:A10="P1")*(B1:B10="red")*(D110=20),E1:E10))

Better to use cells to hold the criteria:

G1 = P1
H1 = red
I1 = 20

=AVERAGE(IF((A1:A10=G1)*(B1:B10=H1)*(D110=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.

 

[TGV]

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

 

[TGV]

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

 

[TGV]

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

 

[Max]

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