Excel Formula

Occasional Contributor

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

Criteria CountAvg.User.Per.Count????
>2500 1 
>=500 <=2500 9 
<500 10 
8 Replies
best response confirmed by Sergei Baklan (MVP)


In D25:


In D26:


In D27:


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


Can you provide an example of what you want?

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


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

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


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.