# Average Based On Conditions

#### varunwalla

##### New Member
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
ABCDEFG
1NameTypeQtypricetotalNameAverage
2Bobprofit5198.00990Bob198
3Remoprofit308.85265.5Remo9.027778
4Remoprofit58.7543.75
5Johnaprofit1016.35163.5
6Remoloss339.15301.95
7Remoloss229.15201.3
8Johnaprofit3423.00782
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=SUMIF(\$A:\$A,\$F2,\$E:\$E)/SUMIF(\$A:\$A,\$F2,\$C:\$C)
E2:E8E2=(C2*D2)

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### AhoyNC

##### Well-known Member
Try:
Also, it's best not to reference whole columns in your formulas. If data will only go to say row 5000 then use something like A2:A7000.

Book1
ABCDEFG
1NameTypeQtypricetotalNameAverage
2Bobprofit5198990Bob198
3Remoprofit308.85265.5Remo8.835714286
4Remoprofit58.7543.75
5Johnaprofit1016.35163.5
6Remoloss339.15301.95
7Remoloss229.15201.3
8Johnaprofit3423782
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=SUMIFS(E:E,A:A,\$F2,B:B,"<>"&"loss")/SUMIFS(C:C,A:A,\$F2,B:B,"<>"&"loss")
E2:E8E2=(C2*D2)

works perfectly

#### varunwalla

##### New Member
is it possible to return average zero if the profit and loss are same i.e remo profit 35 & loss 35 so the average return zero in g3

#### AhoyNC

##### Well-known Member
I'm probably missing something here, but I don't see where the profit and loss for Remo is the same.

#### varunwalla

##### New Member
i mean if the qty becomes equal something like this remo profit 35 and loss 35

Replies
2
Views
41
Replies
2
Views
95
Replies
5
Views
94
Replies
21
Views
528
Replies
1
Views
101

1,129,942
Messages
5,639,106
Members
417,072
Latest member
JaimeDee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back