Filter function with IF statement in 1st parameter to perform fill down multiplication OR division

Question

Link to my previous related question for more background if needed: Transpose unique rows to columns while IF statement conditions are met but do not consolidate

My table headers start on row 20 Column A, and the table ends at row 36. I highlighted in red the problem my current formula is creating. All data is rand() generated.

Background: I want the filter function to multiply or divide, depending on the situation, the selected transposed qty column by its respective usage ratio. Looking at FG 7120, the three transposed component quantities are 4,061, 4,744, and 10,396 respectively.

If usage ratio >= 1 I want to divide by that usage ratio and fill down the same output; if usage ratio < 1 I want to multiply the quantity by usage ratio and fill down the output. The rational logic behind this is that say a component material requires 2 components to make 1 FG, and you have 800 component qty, that quantity can only produce 400 FG in real case at best. Hence trying see how many FG can be made by zeroing out each component material in a scenario analysis.

In the first filter output column, the formula should have returned [4061*.25] = 1015.25 for all 4 rows related to FG 7120, but something in formula caused the wrong output in the third row of the filter output (highlighted red cell).

The second filter output column should have returned [4744/2] = 2372 and filled down for all rows related to FG 7120. The green cells in the ideal state table show this. The remaining columns are just a repeat of issue one or two happening ad nauseam.

What I did:

 =IF(AND(A21<>B21,D21>=1),TRANSPOSE(FILTER($F$21:$F24/$D$21:$D24,($A$21:$A24<>$B$21:$B24)*$A$21:$A24=$A21)),TRANSPOSE(FILTER($F$21:$F24*$D$21:$D24,($A$21:$A24<>$B$21:$B24)*$A$21:$A24=$A21)))

What am I doing wrong here?

Problem Table

Ideal Table

  19   A       B        C       D        E      F
  20  FG  Component  Plant UsageRatio  Std.$   Qty
  21  7120  7120      1220    1.00     4.71   9783
  22  7120  9907062   1220    0.25     7.8    4061
  23  7120  1007710   1220    2.00     5.74   4744
  24  7120  6670299   1220    0.67     5.18   10396
  25  5492  5492      1220    1.00     2.82   12172
  26  5492  9729374   1220    2.02     5.61   11762
  27  5492  9042677   1220    0.25     5.31   5697
  28  9030  9030      1297    1.00     7.69   6659
  29  9030  6367234   1297    2.23     5.94   14463
  30  9030  3858464   1297    1.87     4.61   5853
  31  9030  8613075   1297    2.44     6.6    3435
  32  9030  9473775   1297    1.61     2.55   4371
  33  7774  7774      1285    1.00     2.55   43
  34  7774  4345      1285    2.23     2.55   5235
  35  7774  324324    1285    0.33     2.55   3241
  36  7774  5345      1285    1.76     2.55   5435

EDIT-2 Hid some columns but left in Column letters

   A       B         D         F       G           H             I  
   FG   Component UsageRatio  Qty               
   7120 7120        1.00      9783    1,015        2,372       6,965    
   7120 9907062     0.25      4061   [1015*.25]  [2372*.25]   [6965*.25]    
   7120 1007710     2.00      4744   [1015/2]    [2372/2]     [6965/2]  
   7120 6670299     0.67      10396  [1015*.67]  [2372*.67]   [6965*.67] 

Answer 1

It would be best if the usage ratio were all similar values (i.e. divisors or multipliers), but if you must have them in their current format, then apply the IF as part of the multiplied value, like this:

=TRANSPOSE(FILTER($F$21:$F$24*IF($D$21:$D$24>=1,1/($D$21:$D$24),$D$21:$D$24),($A$21:$A$24<>$B$21:$B$24)*$A$21:$A$24=$A21))

EDIT:

To apply the usage ratio to each row after the existing transformation, simply multiply the whole thing by the IF statement within:

=TRANSPOSE(FILTER($F$20:$F$23*IF($D$20:$D$23>=1,1/($D$20:$D$23),$D$20:$D$23),($A$20:$A$23<>$B$20:$B$23)*$A$20:$A$23=$A20))*IF($D$20:$D$23>=1,1/($D$20:$D$23),$D$20:$D$23)

Note that you need only enter this formula once in the top-left cell of the spill range. You don't need to drag it down.

EDIT 2:

Enter this on the top row then drag down:

=TRANSPOSE(FILTER($F$2:$F$13*IF($D$2:$D$13>=1,1/($D$2:$D$13),$D$2:$D$13),($A$2:$A$13<>$B$2:$B$13)*$A$2:$A$13=$A2))*IF($D2>=1,1/($D2),$D2)