Forum Discussion
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 |
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")
8 Replies
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)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
Can you provide an example of what you want?
