ehsichaudhry
New Member
- Joined
- Jun 28, 2021
- Messages
- 1
- Office Version
- 2013
- Platform
- Windows
here is two types of transactions on a single sheet i.e. sales and purchase. In Avg Column I want the average price of that particular item that is present in product-id column. I have managed to calculate avg cost if only purchases are made in the sheet. But as soon as I make a sale things get messy.
My required output for Avg Column is 2, 2.8 ,2.8 and 3 respectively.
My sheet works like I made a purchase P-09 bought 3 quantity of item at 2$. Average cost (In Avg Column) will be 2$. Then I made a purchase P-25 bought 2 quantity @ 4$. Avg will be 2.8. Then I Sold all the quntity in my stock (Mostly will sell a number of items not the whole stock). So Avg cost is still 2.8. Then after this sale transaction i have no items left so my avg cost is 0. but when i again purcahse some quantity of that item the average cost should be 3$.
For better understanding suppose i sold 4 items instead of 5(total number of items) as it is also a possibility that my stock never goes down to 0.
You can use VBA if possible and macros too.
If you want file can send me email at ehsichaudhry@gmail.com
My required output for Avg Column is 2, 2.8 ,2.8 and 3 respectively.
My sheet works like I made a purchase P-09 bought 3 quantity of item at 2$. Average cost (In Avg Column) will be 2$. Then I made a purchase P-25 bought 2 quantity @ 4$. Avg will be 2.8. Then I Sold all the quntity in my stock (Mostly will sell a number of items not the whole stock). So Avg cost is still 2.8. Then after this sale transaction i have no items left so my avg cost is 0. but when i again purcahse some quantity of that item the average cost should be 3$.
For better understanding suppose i sold 4 items instead of 5(total number of items) as it is also a possibility that my stock never goes down to 0.
You can use VBA if possible and macros too.
If you want file can send me email at ehsichaudhry@gmail.com
