SOLVED

# Excel Formula

Occasional Contributor

# Excel Formula

Can anyone help me with a formula?  I've inherited one but it doesn't work & I'm pulling my hair out.

I am trying to calculate the Avg.ValueofUsers.Per.Count -Example below

 Value Users 275 8 250 6 3950 220 465 15 1860 44 700 18 1500 30 424 12 1890 55 300 6 240 6 320 8 1533 38 1650 33 405 9 575 10 500 8 415 8 495 15 1803 55 19550 604 Criteria Count Avg.User.Per.Count???? >2500 1 >=500 <=2500 9 <500 10
8 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Excel Formula

In D25:

=SUMIFS(\$C\$2:\$C\$22,\$C\$2:\$C\$22,">2500")/SUMIFS(\$D\$2:\$D\$22,\$C\$2:\$C\$22,">2500")

In D26:

=SUMIFS(\$C\$2:\$C\$22,\$C\$2:\$C\$22,">=500",\$C\$2:\$C\$22,"<=2500")/SUMIFS(\$D\$2:\$D\$22,\$C\$2:\$C\$22,">=500",\$C\$2:\$C\$22,"<=2500")

In D27:

=SUMIFS(\$C\$2:\$C\$22,\$C\$2:\$C\$22,"<500")/SUMIFS(\$D\$2:\$D\$22,\$C\$2:\$C\$22,"<500")

# Re: Excel Formula

Hi Hans, many thanks for that - although I am trying to get the Average Value per count based on criteria in one formula

# Re: Excel Formula

Can you provide an example of what you want?

# Re: Excel Formula

Hi Hans,
In this example, there is a
Count of 10 values <500.
Total Value = 5364.
There is a Total of 129 Users for each of the Values <500.
The figure I'm looking for is 42. (=5364/129)

All this to be calculated in D27 as one formula

# Re: Excel Formula

The formulas that I posted do exactly that.

But in your example, the total of values < 500 is 3589, not 5364. And the total number of users with value < 500 is 93, not 129. So the formula for <500 returns 3589/93 = 38.6

# Re: Excel Formula

Apologies, my mis-calculation

# Re: Excel Formula

Hi Hans, I've populated my SS & the formula works perfectly! You're a star! Thank you.

# Re: Excel Formula

Another approach to obtaining the counts is to use FREQUENCY.  Versions of Excel supporting dynamic arrays may enter it as is and Excel will calculate.  Older versions must input the formula as a multi-cell array: select three cells, input formula, ctrl+shift+enter.

=FREQUENCY(C2:C22,F2:F3)