varunwalla
New Member
 Joined
 Aug 13, 2020
 Messages
 32
 Office Version

 365
 2019
 Platform

 Windows
 Mobile
 Web
Hello everyone,
I need to calculate the average price of a product let say Orange.
At the moment i am using this procedure to calculate the average value
is there any techniques to simplify this for each product i am manuly entering the reference cells for apple i am entering it as
is there any formula to simplify these steps
Any help would be much appreciated
I need to calculate the average price of a product let say Orange.
 First 30 Qty of Orange is bought at 8.85 so the value becomes 265.50
 Next 5 Qty of Orange is bought at 8.75 so the value becomes 43.75
 Total cost of 35 Qty is 309.25
 Now taking the average value of 35 is 8.84 ( rounding to 2 digit ) i.e 309.25 divide by 35
At the moment i am using this procedure to calculate the average value
Code:
=SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Orange")
is there any techniques to simplify this for each product i am manuly entering the reference cells for apple i am entering it as
Code:
=SUMPRODUCT(E2+E5)/(C2+C5)*(B5="Apple")
is there any formula to simplify these steps
Sumproduct.xlsx  

A  B  C  D  E  F  G  H  I  
1  S.No  Product Name  Quantity  Price  Total Price  Average Price Cost  Total Investment Cost  
2  1  Apple  5  198.00  990.00  Orange  8.84  Orange  
3  2  Orange  30  8.85  265.50  Apple  204.00  Apple  
4  3  Orange  5  8.75  43.75  
5  4  Apple  5  210.00  1050.00  
Sheet 1 
Cell Formulas  

Range  Formula  
G2  G2  =SUMPRODUCT(E3+E4)/(C3+C4)*(B3="Orange") 
G3  G3  =SUMPRODUCT(E2+E5)/(C2+C5)*(B5="Apple") 
A3:A5  A3  =(A2+1) 
E2:E5  E2  =(C2*D2) 
Any help would be much appreciated