Average of multiple values in same column range and in different columns range

M_Shweta

New Member
Joined
Aug 13, 2014
Messages
4
I have to find average number of days if status = new or open,severity= 4,area=Apps

Demo table
Area Severity Status No of days

Apps 4 new 20
Apps 3 open 10
Node 3 close 0
Device 2 new 25
Apps 4 open 12
Device 1 close 0
Node 4 new 13


I have tried =Average(if((A:A="Apps")*(B:B="4")*(C:C="new")+(C:C="open"),D:D)) cntrl+shft+enter but i am getting incorrect answer when using averageifs () i am getting div/0 error.Please help me to fix this issue.Thank you:)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Control+shift+enter, not just enter:

=AVERAGE(IF(A2:A8="Apps",IF(B2:B8=4,IF(ISNUMBER(MATCH(C2:C8,{"new";"open"},0)),D2:D8))))
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
Apps4new20
Apps3open10
Node3close0
Device2new25
Apps4open12
Device1close0
Node4new13
32
=SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="new")*(D1:D7))+SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="open")*(D1:D7))

<colgroup><col><col><col><col><col span="11"></colgroup><tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Thanks for the quick response but again i got Div/0 error :(

How did you manage that?

AreaSeverityStatusNo of days Avg
Apps4new20 16
Apps3open10
Node3close0
Device2new25
Apps4open12
Device1close0
Node4new13

<COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2560" width=72><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3100" width=87><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3811" width=107><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

F2:
Rich (BB code):
=AVERAGE(IF(A2:A8="Apps",IF(B2:B8=4,IF(ISNUMBER(MATCH(C2:C8,{"new";"open"},0)),D2:D8))))
which you must confirm by pressing down the control and the shift keys at the same while you hit enter.

See the workbook that implements this formula:
https://dl.dropboxusercontent.com/u/65698317/M_Shweta%20multiconditional%20average.xlsx
 

M_Shweta

New Member
Joined
Aug 13, 2014
Messages
4
Apps4new20
Apps3open10
Node3close0
Device2new25
Apps4open12
Device1close0
Node4new13
32
=SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="new")*(D1:D7))+SUMPRODUCT((A1:A7="apps")*(B1:B7=4)*(C1:C7="open")*(D1:D7))

<tbody>
</tbody>
Sir the answer should be 16 :eek:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,495
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top