SOLVED

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

  ValueUsers
  2758
  2506
  3950220
  46515
  186044
  70018
  150030
  42412
  189055
  3006
  2406
  3208
  153338
  165033
  4059
  57510
  5008
  4158
  49515
  180355
  19550604
    
Criteria CountAvg.User.Per.Count????
>2500 1 
>=500 <=2500 9 
<500 10 
8 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Nettie_1810 

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")

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

@Nettie_1810 

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

@Nettie_1810 

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.

@Nettie_1810 

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)

Patrick2788_0-1669125743411.png