Forum Discussion
Challenge 3
Hello Every Body
I have problem in applying the formula for following challenge;
I have some items which we need find their Stock quantity,
the selling quantity is mentioned, but for purchase it is repeated for each cod, For example for A item we bought 450 PC , and sold 290 so its stock is 160 Pc,
i want that this Stock Quantity repeated in Stock Columns, As I manually filled is columns,
I attached Excel file
Thank you for your Guide in advance
Okay, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
When you successfully confirm a Formula as an Array formula by confirming it with keystrokes Ctrl+Shift+Enter, you will find that the formula in the formula bar gets surrounded by the curly brackets.
In I8
=SUM(IFERROR((--(FREQUENCY(IF($E$8:$E$27=E8,MATCH($D$8:$D$27&$E$8:$E$27,$D$8:$D$27&$E$8:$E$27,0)),ROW($D$8:$D$27)-ROW($D$8)+1)>0))*($G$8:$G$27),0))-SUMIF($E$8:$E$27,E8,$F$8:$F$27)Don't forget to confirm this with Ctrl+Shift+Enter and then copy it down.
3 Replies
In the first description in J8 which says this...
For A : Purchase(200+250)-sum(Selling Quantity)=160
Where does this part (200+250) comes from? What is 200 and what is 250 here and how do you calculate this?
If you calculate this from a range of cells, please mention the source range for this calculation.
Also does code has any role in this calculation of only Items are considered in this calculation?
Where does this part (200+250) comes from? What is 200 and what is 250 here and how do you calculate this? Its a Purchase Quantity, Two time we have had Purchase which is repeated in this column, (the purchase quantity repeated till the Sum selling quantity be equal to purchase quantity for Each Purchase Code for each item)
200: purchase quantity for Code 102 and 250 Purchase quantity for Cod 202 for item A in total we have 450 Pc for item AOkay, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
When you successfully confirm a Formula as an Array formula by confirming it with keystrokes Ctrl+Shift+Enter, you will find that the formula in the formula bar gets surrounded by the curly brackets.
In I8
=SUM(IFERROR((--(FREQUENCY(IF($E$8:$E$27=E8,MATCH($D$8:$D$27&$E$8:$E$27,$D$8:$D$27&$E$8:$E$27,0)),ROW($D$8:$D$27)-ROW($D$8)+1)>0))*($G$8:$G$27),0))-SUMIF($E$8:$E$27,E8,$F$8:$F$27)Don't forget to confirm this with Ctrl+Shift+Enter and then copy it down.
