• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get average cost of remaining (unsold) stock?

unsethi

New Member
If you use Excel tables and table references, then references will update as the table grows and shrinks. :)
Whether the feature of Table is available in Google Sheets also?
I have been using it on both the platforms and in Sheets when I add a new row at the bottom, it does not contain the formula automatically and I need to fill the same.
 

unsethi

New Member
I believe the formula you require is
= SUM( IF( SQntyTP>0, SQntyTP * (SRateTP - BRateTP) ) )

I have used a Table for the data and implemented the calculation as a named formula, but whether it simply mystifies you further I am not sure :confused:?
Please check I have written some issues at the bottom of the sheet.
 

Attachments

Peter Bartholomew

Well-Known Member
Agreed. I hadn't realised that the sale part of the line did not necessarily apply to the items bought on the left (buying 30 and selling 40 would be a good trick though!) What version of Excel do you use? Most of the work I do is only applicable to Office 365.
 

unsethi

New Member
Agreed. I hadn't realised that the sale part of the line did not necessarily apply to the items bought on the left (buying 30 and selling 40 would be a good trick though!) What version of Excel do you use? Most of the work I do is only applicable to Office 365.
I am using 2007.
What I showed in the file can be done? Getting part of the cell value and getting the desired result?
 

Peter Bartholomew

Well-Known Member
Could well be. I hadn't understood that the quantity sold against a given serial number line could exceed the quantity bought. I will take a look.
 

unsethi

New Member
If you will look at the dates you would understand.
If buying is on four occassion in a year and if complete stock sold on a single date in the next year. Then it can happen.
 

unsethi

New Member
Can there be any solution with this regard?
Or else I have to enter the selling details with respect to buy quantity in different rows.
 

Peter Bartholomew

Well-Known Member
It took a while but I think I understood what you were looking for eventually.
Part of the calculation for total profit required the purchase cost of goods sold which also tells one the cost of stock still to be sold.
Hopefully we are just about getting there.

By the way, may I use your problem to demonstrate techniques using modern dynamic arrays? Accumulations such as that which your problem requires is a problem area for DA since they treat the calculation as a circular reference.
 

Attachments

Last edited:

unsethi

New Member
It took a while but I think I understood what you were looking for eventually.
Part of the calculation for total profit required the purchase cost of goods sold which also tells one the cost of stock still to be sold.
Hopefully we are just about getting there.

By the way, may I use your problem to demonstrate techniques using modern dynamic arrays? Accumulations such as that which your problem requires is a problem area for DA since they treat the calculation as a circular reference.

Thank you for the new formula and it is giving the correct answer.
But some more issue I witnessed are-

1. When I tried to expand the table by a row, both the values RSAvgRate and profit giving Error #Value!

2. I am using this excel on google sheets also for getting live prices by using (=GOOGLEFINANCE) but the table formula (used in Excel) not giving answers there. And on the contrary, if I continue with Excel only I am unable to fetch the live prices which I am getting easily on Google Sheets. Google Finanace formula not working on Excel Sheet. Is there any other way to fetch the live rates on excel sheets from any other website?

Yes, Of Course. You can use my problem. You have helped me a lot and have boosted confidence in me for using Excel more efficiently. If it helps some more people, it is good. :)
 

Peter Bartholomew

Well-Known Member
The error should sort itself once the Buy Amount and Quantity are entered. Because I have used "" in the rate calculation for missing data, the calculation for stockCost
= SUM( Table1[BQntyTP] * Table1[BRateTP] )

errors. Returning 0 for the rate, rather than "", would sort it but, looking at the formula for stockCost now, the calculation looks unnecessarily complicated
= SUM( Table1[BAmtTP] )
would do just as well. This is also a standard calculation that can be performed by any Table by switching on the Total row.

As for returning stock prices, that is outside my knowledge. In Office 365 there are stock prices 'powered by Bing'. From Excel 2010 it has been possible to use Power Query but I wouldn't know the data source; and the Excel version is still too recent for you. Google has been web-centric for much longer than Microsoft Office.

Perhaps you should post a new question for help with the data source. Help with Google sheets may require moving to a different forum.
 
Top