• 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?

Status
Not open for further replies.
AliGW said:
If you use Excel tables and table references, then references will update as the table grows and shrinks. :)
Click to expand...

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.
 
Peter Bartholomew said:
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:?
Click to expand...

Please check I have written some issues at the bottom of the sheet.
 

Attachments

  • Temp Profit.xlsx
    15.8 KB · Views: 3
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.
 
Peter Bartholomew said:
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.
Click to expand...

I am using 2007.
What I showed in the file can be done? Getting part of the cell value and getting the desired result?
 
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.
 
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.
 
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.
 
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

  • FIFO Profit (PB).xlsx
    16.7 KB · Views: 30
Last edited:
Peter Bartholomew said:
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.
Click to expand...


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. :)
 
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.
 
Hi All,

Sorry to disturb.
If below question is out of scope, please forgive me.

But may I know why the buying average avg.rate is 65.50, not 62.27 ??
I combined all buy and sell information in a table, and did a calculation row by row. If I consider buy and sell in many transaction, the different will become bigger and bigger.

Is my fault in below calculation?

Bill AmountRateQuantity actionOutstanding QtyAvg. Rate
2,182.6872.7630Buying3072.76
2,586.0086.2030Buying6079.48
2,457.0081.90-30Selling3079.48
2,004.64100.2320Buying5087.78
932.2993.2310Buying6088.69
1,683.9584.2020Buying8087.57
1,090.1121.8050Buying13062.27

thanks a lot!
 
@enterenter,

Don't hijack the other person's thread.

Please open a new thread for your own question together with the expected result and attachment.

This thread is closed.
 
Status
Not open for further replies.
Back
Top