varunwalla
New Member
- Joined
- Aug 13, 2020
- Messages
- 32
- Office Version
- 365
- 2019
- Platform
- Windows
- Mobile
- Web
How can i find the average based on certain conditions i.e if loss is present in b column it should not be calculated in average and should be ignored in g column here g3 column. so the average of remo should be 8.835714286
Average.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Name | Type | Qty | price | total | Name | Average | ||
2 | Bob | profit | 5 | 198.00 | 990 | Bob | 198 | ||
3 | Remo | profit | 30 | 8.85 | 265.5 | Remo | 9.027778 | ||
4 | Remo | profit | 5 | 8.75 | 43.75 | ||||
5 | Johna | profit | 10 | 16.35 | 163.5 | ||||
6 | Remo | loss | 33 | 9.15 | 301.95 | ||||
7 | Remo | loss | 22 | 9.15 | 201.3 | ||||
8 | Johna | profit | 34 | 23.00 | 782 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G3 | G2 | =SUMIF($A:$A,$F2,$E:$E)/SUMIF($A:$A,$F2,$C:$C) |
E2:E8 | E2 | =(C2*D2) |