Nov 22 2022 02:16 AM - edited Nov 22 2022 02:19 AM
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 |
Nov 22 2022 02:31 AM
SolutionIn 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")
Nov 22 2022 02:47 AM
Nov 22 2022 04:09 AM
Nov 22 2022 04:19 AM
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
Nov 22 2022 05:19 AM
Nov 22 2022 06:03 AM
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)
Nov 22 2022 02:31 AM
SolutionIn 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")