Forum Discussion
Problem with formula
Hello i need help with my Excel. like i show elowe in printscreen i need to make delta of Stock and demanding quantity but how to make it for indexes that are dubbled to take stock - first demand and then use the stock that left and subtract it from second demand
I mean for index 1311117 i have on stock 833000 (B5) and after first demand i will have left 496040 (D5) and use this 496040 (D5) to subtract it from 51840 (C6).
I know that i can do it by hand but i trying to automate it and make it that so column D will have right answers neither i have unique index or i have 2 or more
Hi AdrianMgdal
With data formatted as Table (not mandarory):
in D2 (can be shortened):
=LET( i, COUNTIF(A$2:A2, [@Index]), f, FILTER([Demand Qty], [Index]=[@Index]), s, SUM(TAKE(f, i)), [@[Stock Qty]] - s )=IF(COUNTIF($A$2:A2,A2)=1,B2-C2,INDEX($E$1:E1,LARGE(IF($A$1:A1=A2,ROW($A$1:A1)),1))-C2)You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The index numbers don't have to be entered in consecutive rows. For example index 13111129 is in cells A11, A12, A13 and A15 or row 11, 12, 13 and 15.
