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

Find OUT KG Occurrance an IN KG then Multiply with Price

Dear Excel Genius

In the attached excel sheet A1 to A26 OUT KG, B1 to B26 IN KG, C1 to C26 Price$. I need OUT KG Price in Column D. Manually I calculated 4 cell values. Also, F Column I mentioned those 4 rows OUT KG on where to occur an IN KG. After finding the IN KG cells, multiply them with respective row prices. then the D column Values get filled.

83003
 

Attachments

  • IN OUT Price.xlsx
    11.3 KB · Views: 1
Only D1 is clear enough, lost since D2, the same with column F,​
hoping someone else have time to decypher and may see the light …​
 
First OUT 10 minus from First IN 15 then remaining 5 in First IN
Second OUT 20 takes the remaining 5 from first IN, then 15 from second IN 15 then the remaining 11 in Second IN
likewise, each OUT Uses IN and is multiplied by the prices which are D Column Values.

the below formula gives D column Values in E. Check the attachment. I need VBA Code to get the same output.

=LET(q,$A$1:$A$27,p,$C$1:$C$27,s,$B$1:$B$27,sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),),ROWS(E$1:E1)))
 

Attachments

  • IN OUT Price.xlsx
    13.4 KB · Views: 2
It seems you have a more recent version than on my tests laptop so I can't help this way.​
Indicate at least the version you use for anyone who may give it a try …​
 
I am using Office 365 Excel For Mac. the excel formula is given by @Excel Wizard.

I try to convert the same to VBA Code as my worksheet slows where I have 100 IN, Price, and OUT with 10,000 Rows.

I need the same output with a faster VBA Code.
 
So using this formula under VBA should take a bit more time …​
According to your post #5 attachment, let's see the 3 cells D13:D15 : are you able to explain each value used within the formulas,​
how do you calculate them manually ?​
D13 = 126*C12+4*C13​
D14 = 140*C13
D15 = 3*C13+147*C14
 
For D13, A13 OUT required 130 from IN.
Till A12 sum of OUT is 780, So above 780 matched IN till B12 sum of 906, then 906-780=126*C12+4*C13

For D14, A14 OUT required 140 from IN.
Till A13 sum of OUT is 910, So above 910 matched IN till B13 sum of 1053, then 1053-910=143. we take 140 only as we required an A14 value of 140. 140*C13

For D15, A15 OUT required 150 from IN.
Till A14 sum of OUT is 1050, So above 1050 matched IN till B13 sum of 1053, then 1053-1050=3*C13+147*C14
 
Back
Top