• 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.
U

unsethi

New Member
Here is my excel sheet having buying details on the left and the selling details on the right.
A2 is the total buying average.
I2 is the total selling average.
E2 is the remaining Stock.
E4 is the Cell where I need to get the desired result of average cost of remaining (unsold) stock.

I have tried to explain it while using the data of rows 6 and 7. The average buying cost of remaining (unsold) stock.

=((C6*(B6-H6)) + (C7*(B7-H7)))/((B6-H6)+(B7-H7))

Is there any formula or shortcut method to get the desired result for whole of the sheet data, i.e. average cost of all remaining (unsold) stock.

67615
 

Attachments

  • Excel Forum.xlsx
    11.3 KB · Views: 34
I would suggest
= SUM(buy.quantity) - SUM(sell.quantity)
for the remaining stock and
= (SUM(buy.amount) - SUM(sell.amount)) / (SUM(buy.quantity) - SUM(sell.quantity))
for the average price of remaining stock.

Do NOT forget to keep both the Excel forum and this one (as well as any other?) informed of positive results!
 
unsethi
As a new member, You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
Peter Bartholomew said:
I would suggest
= SUM(buy.quantity) - SUM(sell.quantity)
for the remaining stock and
= (SUM(buy.amount) - SUM(sell.amount)) / (SUM(buy.quantity) - SUM(sell.quantity))
for the average price of remaining stock.

Do NOT forget to keep both the Excel forum and this one (as well as any other?) informed of positive results!
Click to expand...

Deducting sell amount from buy amount would not help as it would deduct the profit of sold quantity from remaining stock prices which will eventually give the profit/loss adjusted buying average.



Sorry, for posting it on multiple forum. I will surely post the answered solution on all the forums.
 
vletm said:
unsethi
As a new member, You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Click to expand...

I have gone through the rules now. It won't happen again but please someone give me the solution.
 
unsethi
Good that You have reread it
as well as You've noticed that You'll post after Solution.
Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Sorry about that; brain out of gear! I think
= SUM( (1-sell.quantity/buy.Quantity)*buy.amount ) / SUM(buy.Quantity-sell.quantity)
would be closer to the mark. The first sum is over the proportion remaining multiplied by the purchase cost and the second sum is the remaining stock.

If you are using anything but the latest 365 version of Excel the formula will need to be committed with CSE as an array formula. The alternative is to define a named formula e.g. averageCost to hold the formula. Any formula that is not on the grid is and always was evaluated as an array calculation.
 

Attachments

  • Cost of stock (PB)).xlsx
    13.6 KB · Views: 32
Fluff13 said:
Also posted here https://www.mrexcel.com/board/threads/how-to-get-average-of-remaining-unsold-stock.1132985/
Click to expand...


This is the last.. I had posted it on three forums simultaneously. I will post the solution everywhere as soon as I get it on any forum.
Peter Bartholomew said:
Sorry about that; brain out of gear! I think
= SUM( (1-sell.quantity/buy.Quantity)*buy.amount ) / SUM(buy.Quantity-sell.quantity)
would be closer to the mark. The first sum is over the proportion remaining multiplied by the purchase cost and the second sum is the remaining stock.

If you are using anything but the latest 365 version of Excel the formula will need to be committed with CSE as an array formula. The alternative is to define a named formula e.g. averageCost to hold the formula. Any formula that is not on the grid is and always was evaluated as an array calculation.
Click to expand...


That's really great. It is very correct. But I have an issue. I am unable to use named range as I have multiple sheets in one workbook. So I have to name range on every worksheet.
I tried using the formula using the cell reference but the result is different from yours.
The same values I used but getting different result -
=SUM((1-H2/B2)*SUM(A6:A11))/E2

Can you please write the formula without reference to Named Range and with reference to Cells only.
Or is there any way to name the same range differently on different sheets in one go?
 
Hi @unsethi . I have described the practice of direct referencing of cells by location as an abomination that should never have entered the world of serious spreadsheet building :eek:. Reading between the lines you may gather that I am not totally enamoured of the idea.

One way of unravelling the solution to use direct referencing, is to select Formulas / Use in Formula / Paste Names ... to create a list of names along with the formulas they refer to. You can then substitute each formula back in place of the name [as the Excel calculation does]. This gives
= SUM( (1 - $H$6:$H$11/$B$6:$B$11) * $A$6:$A$11 ) / SUM($B$6:$B$11 - $H$6:$H$11)

Unfortunately, moving the formula from the defined name to the grid allows the traditional spreadsheet to make a further dog's breakfast of the calculation. Instead of performing the calculations as written, Excel will helpfully pick out a single cell (the one on the same row as the formula) from each array and use that. This process is called implicit intersection.

The way to instruct Excel not to use implicit intersection is to commit the formula using Ctrl+Shift+Enter.

If you wish to evaluate the option of retaining names, one way of achieving that is to copy the initial sheet as a template for further sheets that are intended to perform like calculations. Instead of getting new names, each sheet introduces a further copy of the each name but with scope local to the particular sheet so Sheet2!sell.quantity is a different value from Sheet3!sell.quantity. Any formula on a sheet will pick up the Name local to that sheet if it exists. More information than you ever wished to know …:rolleyes:?
 
I am a novice to MS Excel and trying to learn.
Direct referencing also didn't helped, then I tried in my sheet by naming range as you did in your sheet.
Then I tried to apply same formula by changing the your named range to my named range but that gave an error #Value!
I know it might be irritating for you, but please bear with me and guide me through so that I may not indulge in copy/paste and try to learn by applying my self. Named ranges are same as yours but I cut their names short and indicative. Can you please tell what mistake I did?
Here is the screen shot and also the file is attached.
67658
 

Attachments

  • Excel Forum.xlsx
    11.4 KB · Views: 5
Your only error was to miss out on committing the formula with Ctrl+Shift+Enter. That tells Excel that you intend the calculation to apply to the entire column rather than just cells on the current row [your formula gave an error rather than a value because the range references do not intersect the row with the formula]. To use CSE, you write the formula and, with the cursor still in the formula bar, you simultaneously press the Ctrl and Shift keys and, while they are pressed, click Enter. It is a pain; it is easily forgotten; and it strikes me as being hard work just to instruct Excel NOT to select single cells out of your arrays before calculating.

I used the named formula averageCost to get Excel to calculate the array formula without needing CSE. It is not a basic technique; novices or intermediate spreadsheet users will not know it and it may well be that many expert users do not approve. Despite that, I used it on just about every spreadsheet I wrote, until modern dynamic arrays made array calculation the default, so special tricks were no longer needed.

In the attached, I have taken copies of the worksheet and show that what are apparently the same names give different values on different sheets.
 

Attachments

  • Temp.xlsx
    18.2 KB · Views: 18
Last edited:
Ok.
I just tried to use CSE and it gave the same result as yours. Now could you please guide me how to name a formula, so that I don't require to use CSE.
And while naming the formula, would I be able to name it for different sheets, the same way I would be naming the ranges for different sheets?

One more thing, when any new entry would be made in excel will the named range automatically cover that new entry or we have to change the definition of the range?

Could you please give the formula for Sold quantity profit?
 
Last edited:
If you think of the process as two steps, the first is to apply a Name to the cell containing the formula, scoped to worksheet. This can either be done by typing the fully qualified name into the Name Box, e.g.
Sheet1!RSAvgRate
or you can open Name Manager and the New... button. Do not forget to set the Scope as well as entering the Name.

Then replace the cell reference by the formula it contains, first copy the formula from the formula bar. Then reopen Name Manager, select the Name you are about to redefine, and paste the formula into the Refers to box (starting with the '=').

Return to the (previously named) cell and replace the formula with the newly defined name
=RSAvgRate

No, unfortunately defined names do not extend automatically. You would need to extend the range references from Name Manager. The advantage is that any formula that references the name will then update. This is inconvenient, but marginally better than traditional techniques that require any formulas that use direct references to be located and updated individually.

There are ways of making Named Ranges dynamic by using the OFFSET function in a formula but, since 2007, Excel Tables offer a better route to creating dynamic ranges. I have introduced the latter on the second sheet.
 

Attachments

  • Temp.xlsx
    20.1 KB · Views: 14
Guido
True the normal use case is that one continues to add data to the point where the solution collapses under its own weight!
For solutions that are truly dynamic though, it makes sense to replace old input by new and reuse an existing solution for new problems. If the new dataset is accessed through Power Query the Table will resize. Otherwise there is a manual step to adjust the resize button (more of a fly speck than a button but I don't know what else to call it).

Maybe it is traditional spreadsheet practice that is not really built for reuse?
 
Peter Bartholomew said:
If you think of the process as two steps, the first is to apply a Name to the cell containing the formula, scoped to worksheet. This can either be done by typing the fully qualified name into the Name Box, e.g.
Sheet1!RSAvgRate
or you can open Name Manager and the New... button. Do not forget to set the Scope as well as entering the Name.

Then replace the cell reference by the formula it contains, first copy the formula from the formula bar. Then reopen Name Manager, select the Name you are about to redefine, and paste the formula into the Refers to box (starting with the '=').

Return to the (previously named) cell and replace the formula with the newly defined name
=RSAvgRate

No, unfortunately defined names do not extend automatically. You would need to extend the range references from Name Manager. The advantage is that any formula that references the name will then update. This is inconvenient, but marginally better than traditional techniques that require any formulas that use direct references to be located and updated individually.

There are ways of making Named Ranges dynamic by using the OFFSET function in a formula but, since 2007, Excel Tables offer a better route to creating dynamic ranges. I have introduced the latter on the second sheet.
Click to expand...


Extension of the formula for the whole of the column has been solved by the Table creation.
Regarding RSAvgRate, I am afraid that I couldn't understand what you tried to say?

I wish to have formula for calculation profit of the sold units.
Is that possible to have? I have tried hard but failed. Please guide.
 
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:?
 

Attachments

  • Temp.xlsx
    16.1 KB · Views: 16
Last edited:
Status
Not open for further replies.
Back
Top