Hi, I've asked this question elsewhere but although the answer came close to what I require, it still isn't exactly right.
In the attached workbook on the Master page in BI2 is the following formula
=LET(x,IF((A4:A85=1)*(BB4:BB85>3)*(AQ4:AQ85<=BG3),D4:D85)*SEQUENCE(,1000,0.1%,0.1%),XMATCH(BH87,MMULT(TRANSPOSE(ROW(D4:D85)^0),IF(x>1,1,x)),-1)/1000)
What it is attempting to do is increase by percentage the figures in column BI4:BI85 so that BI87 is = to BH87.
As you can see from the workbook the result of the formula in BI2 is 1.80% which results in the difference between BI87 and BH87 to 0.4, so very close. If the result had been 1.90% it would have been even closer, however 1.87% would have provided the correct result
So if I manually change the figure in BI2 to 1.87% then the figures in BI87 and BH87 are equal.
Can either the formula in BI2 be amended to produce the exact result I require or maybe a different approach altogether with a different formula?
Or can't this be done with an excel formula?
By using GoalSeek you'll discover that the value in BI2 must be 0.018667917. You need to be accurate to the 9th decimal.
Your sequence that generates an 82 rows X 1,000 columns array starting at 0.1% with steps of 0.1% is way to inaccurate. You'll need 100,000 columns starting at 0.0001% and steps of the same magnitude and divide by 1,000,000, to come reasonably close. Then, the totals of row 87 both add up to 9.950000 and 9.949511, both rounding to 10 with zero decimals. Close enough?
And I would also like to change the part where you create a sequence of 82 numbers 1 and use the SEQUENCE function rather then "TRANSPOSE(ROW(D4:D85)^0)".
The formula then becomes like this:
=LET(
x, IF(
(A4:A85 = 1) * (BB4:BB85 > 3) *
(AQ4:AQ85 <= BG3),
D4:D85
) * SEQUENCE(, 100000, 0.0001%, 0.0001%),
XMATCH(
BH87,
MMULT(
SEQUENCE(, ROWS(D4:D85), 1, 0),
IF(x > 1, 1, x)
),
-1
) / 1000000
)
