Averageifs two criterias

ApolloID

Well-known Member
Joined
Jun 8, 2010
Messages
769
Hi, I need a formula that will give the average based on two criterias.

In sheet 1 i have:

SupplierDocumentPercentage
supplier 1doc150%
supplier 1doc145%
supplier 1doc190%
supplier 1doc299%
supplier 1doc288%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


In Sheet 2 i need the average for supplier, based on average of documents.

The average for doc1 is 62%, and for doc2 is 94%.

So, the result should be:

supplier 178%

<tbody>
</tbody>


Can this be done?

Thanks,
Appolloid
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Is this what you're after?

=AVERAGE(AVERAGEIFS($C$2:$C$6,$A$2:$A$6,"Supplier 1",$B$2:$B$6,"doc1"),AVERAGEIFS($C$2:$C$6,$A$2:$A$6,"Supplier 1",$B$2:$B$6,"doc2"))
 
Upvote 0
Hi Zakkaroo,
Thank you kindly for your reply and formula.

The result is what i need, but my document has around 30.000 rows, and i need a formula that does not contain "Supplier1" or "doc1...doc2"....etc.
With supplier it's easy, because i can replace "Supplier1" with A1 in which i could put Supplier1. however, i cannot have in formula the document1, because i have so many documents.

The best would be a formula that will look at supplier1, and make the average per each document, like your formula, but without specify the documents names.

Can this be done?
Thanks.
 
Upvote 0
Sheet1, A1:C6, houses the data, the headers included.

Sheet2, A2, houses a criterion value like supplier 1.

In B2 of Sheet2 control+shift+enter, not just enter...

=AVERAGE(IF(ISNUMBER(AVERAGEIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6))),AVERAGEIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6))))

If you would want to install the V() function, we can have:

=AVERAGE(IF(ISNUMBER(V(AVERAGEIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6)))),V()))

VBA of V() is...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function
 
Upvote 0
Hi Aladin,
The first formula works great. It's a little slow, but is working.

Can you please tell me what do you mean with install V() function?

Thanks allot.

Apolloid
 
Upvote 0
Hi Aladin,
The first formula works great. It's a little slow, but is working.

The second formula will be faster because it avoids computing twice.

Can you please tell me what do you mean with install V() function?

Execute Alt+F11.
Choose This Workbook.
Run Insert | Module.
Copy the code of V() and paste it in the empty pane.
Run File | Close and Return...

The second formula can now be run.

Thanks allot.

Apolloid

You are welcome.
 
Upvote 0
Hi Aladin, thank you for your explications.

I receive an error when i put the formula:
"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

And if i hit ok, the "MATCH" from the formula is highlighted.

Can you help me with this issue?

Thanks,
Apolloid.
 
Upvote 0
Hi Aladin, thank you for your explications.

I receive an error when i put the formula:
"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

And if i hit ok, the "MATCH" from the formula is highlighted.

Can you help me with this issue?

Thanks,
Apolloid.

See the workbook that implements both versions, with V() and without: https://dl.dropboxusercontent.com/u/65698317/ApolloID averageifs.xlsm.
 
Upvote 0

Forum statistics

Threads
1,207,169
Messages
6,076,910
Members
446,239
Latest member
Home Nest

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