Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average If with multiple conditions | Excel Discussion (Misc queries) | |||
Average of the last x entries with conditions | Excel Worksheet Functions | |||
Average with multiple conditions | Excel Worksheet Functions | |||
AVERAGE with conditions | Excel Worksheet Functions | |||
average on 2 conditions | Excel Worksheet Functions |