Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Average using Sumproduct
Is there a way to find average based on multiple conditions.
I've been using Sumproduct formula to Sum all the cells but not sure how to use it find average. =SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7) Thanks for your help in advance... -- Karthi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Average using Sumproduct
Try this array formula** :
=AVERAGE(IF($B$4:$AT$4=$C$113,IF($B$5:$AT$5=$C$115 ,$B7:$AT7))) ** 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 "Karthik" wrote in message ... Is there a way to find average based on multiple conditions. I've been using Sumproduct formula to Sum all the cells but not sure how to use it find average. =SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7) Thanks for your help in advance... -- Karthi |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Average using Sumproduct
Hi
=SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7)/SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115)) "Karthik" wrote: Is there a way to find average based on multiple conditions. I've been using Sumproduct formula to Sum all the cells but not sure how to use it find average. =SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7) Thanks for your help in advance... -- Karthi |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Average using Sumproduct
Try an Array formula:
=AVERAGE(IF((B4:AT4=C113)*(B5:AT5=C115),B7:AT7)) Micky "Karthik" wrote: Is there a way to find average based on multiple conditions. I've been using Sumproduct formula to Sum all the cells but not sure how to use it find average. =SUMPRODUCT(--($B$4:$AT$4=$C$113),--($B$5:$AT$5=$C$115),$B7:$AT7) Thanks for your help in advance... -- Karthi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find every 2 col's: SUMPRODUCT, AVERAGE.. | Excel Discussion (Misc queries) | |||
Find every 2 col's: SUMPRODUCT, AVERAGE.. | Excel Discussion (Misc queries) | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
AVERAGE & SUMPRODUCT | Excel Discussion (Misc queries) | |||
Using Sumproduct to Find Average | Excel Discussion (Misc queries) |