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

 

[Gary Keramidas]

h2:h4 are percentages corresponding to scanner 1, 2 and 3
g22:i22 are values corresponding to scanner 1, 2 and 3
column m is the total for scanner1, 2 and 3

it subtracts the scanner % from 1, then multiples that times the value in
g22:i22 and then adds the value in g22:i22 to that to get 100% weighted value.
then it divides that by the scanner total in m2:m4.

so , it needs to multiply any value in g22:i22 by the correct scanner % in h2:h4
and then divide it by the correct scanner's total.

simplified examples:
if there is a value in h22, it has to multiply that * 1-h3 and add g22 and then
divide that value by m3.

if there are multiple values, g22 and h22, it has to sum ((g22*(1-h2) + g22))/m2
and ((h22*(1-h3)+h22))/m3


like i mentioned, i have it done in code and it works fine. the portion up to
the "/" gives the correct result, it's just not dividing my the correct value in
m2:m4.

thanks

 

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