Average Based On Conditions

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. 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
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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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)
 

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
I'm probably missing something here, but I don't see where the profit and loss for Remo is the same.
 

varunwalla

New Member
Joined
Aug 13, 2020
Messages
32
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
i mean if the qty becomes equal something like this remo profit 35 and loss 35
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top