AverageIf

[MixedUpExcel]

Hi All,

I'm familiar with SumIf and CountIf but not AverageIf, so wondering if this is going to work for what I need?

I have 2 columns.

Column 1 - Order No.s (these may appear more than once if orders get split but the original order number remains)
Column 2 - Sales Value

I want to be able to find out what the Average Order Value is based on what the order would be if the Order's hadn't been split.

Basically, [TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Order No.[/TD]
[TD]Sales Value[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]13579[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]

There are 3 Order No.s 12345 - 35; 5678 - 15; 13579 - 45

The average would be 35 + 15 + 45 = 95 / 3 = 31.667

How could I get to that with a formula - the list is over 1000 Order No.s with multiple duplicates.

Thanks for any help.

Simon

 

[dreid1011]

Perhaps something like this?

=SUM(B2:B1000)/SUMPRODUCT(1/COUNTIF(A2:A1000,A2:A1000))

The SUMPRODUCT/COUNTIF is counting the number of unique Order No.

 

[MixedUpExcel]

Hi dreid1011,

Thank you for the quick response.

I've just done a quick test of your formula and seems to work just like you said.

Thank you again for your help.

Simon

 

[dreid1011]

You're welcome, I'm glad that worked for you.

 

[Aladin Akyurek]

More efficient and insensitive to possible blank cells in column A...

=SUM(B2:B5)/SUM(IF(FREQUENCY(A2:A5,A2:A5),1))

Even with whole column references...

=SUM(B:B)/SUM(IF(FREQUENCY(A:A,A:A),1))

 

[dreid1011]

More efficient and insensitive to possible blank cells in column A...

=SUM(B2:B5)/SUM(IF(FREQUENCY(A2:A5,A2:A5),1))

Even with whole column references...

=SUM(B:B)/SUM(IF(FREQUENCY(A:A,A:A),1))

The first thing I looked up mentioned a version using Frequency, but did not show the formula, and I am not very familiar with it either so I had no inclination to come up with it myself. Thank you for that.

 

[MixedUpExcel]

More efficient and insensitive to possible blank cells in column A...

=SUM(B2:B5)/SUM(IF(FREQUENCY(A2:A5,A2:A5),1))

Even with whole column references...

=SUM(B:B)/SUM(IF(FREQUENCY(A:A,A:A),1))

Hi Aladin,

Thanks for your input.

I hadn't realised that the formula 'dreid1011' had proposed would cause a problem if I had blank cells (which I will as I'm going to be setting the range much larger than needed for future proofing).

I've tried both of yours which work fine but the problem I have is that I have the year at the top of the column - eg. 2018, so I'm going to use your second one as it means I can start from A2 / B2 without worrying about the year being counted as well.

I do have to learn 'FREQUENCY' though as it's not one I'm familiar with.

Thanks again to both of you

Simon