help with an array formula

[Gary Keramidas]

i have this array formula that i can't quite finish. maybe it can't be done with
a formula. i already have code that gives me the correct result., just wondering
if this formula may work. maybe sumproduct would work.

=(SUM((1-TRANSPOSE(H2:H4))*G22:I22)+SUM(G22:I22))/TRANSPOSE($M$2:$M$4)

the part up to dividing by the transpose range works. i get the correct cell in
the h2:h4 multiplied by the correct cell in the g22: i22 range.

but it always divides by the value in m2. and in this case, it should be
dividing by the value in m3.

to explain, there in a value in i22(individual scanner 3 wt), it corresponds to
a value in h4 (scanner 3%) and i need it to divide by the value in m4 (scanner 3
total wt)

 

[Peter T]

I don't follow what you want the formula to do, the explanation ("to
explain..") doesn't help without sight of your sheet. FWIW it works if array
entered but I assume that's not right.

What would the correct formula be, without any Transpose, if your three
blocks of cells (2 x Vertical 1 x Horiz) $M$2:$M$4, H2:H4, G22:I22 are
re-located in three horizontal rows $A$1:$C$1, A2:C2, A3:C3, or all
vertically in $A$1:$A$3, B1:B3, C1:C3

Regards,
Peter T

 

[Peter T]

Afraid I'm even more confused than I was before. You have three arrays of
three cells each for use in the formula, one of these is used twice so let's
say four arrays. Two arrays are vertical, two horizontal. So you need to
transpose two of these to make things work, such that 'equivalent' cells are
calculating each other.

At least that's what I thought when I first saw the formula. Yet your
description below is not like that at all. Instead it would appear that one
of the arrays should not be considered as an array but as individual cells,
each of which may (or may not) process all of the values in the other
arrays.

Is it not possible to post an adapted example along the lines I suggested
before, such that Transpose is not involved. Then we can see what the
correct result should be before re-orienting the arrays and reintroducing
Transpose (assuming of course there is a solution).

Regards,
Peter T

 

[Gary Keramidas]

i have code, so don't waste any time thinking about it. i just wondered why the
array formula didn't work, but it's no big deal.
thanks.