Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with an array formula
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) -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with an array formula
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" <GKeramidasATmsn.com wrote in message ... 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) -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with an array formula
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 -- Gary "Peter T" <peter_t@discussions wrote in message ... 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" <GKeramidasATmsn.com wrote in message ... 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) -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with an array formula
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" <GKeramidasATcomcast.net wrote in message . .. 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 -- Gary "Peter T" <peter_t@discussions wrote in message ... 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" <GKeramidasATmsn.com wrote in message ... 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) -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with an array formula
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. -- Gary "Peter T" <peter_t@discussions wrote in message ... 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" <GKeramidasATcomcast.net wrote in message . .. 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 -- Gary "Peter T" <peter_t@discussions wrote in message ... 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" <GKeramidasATmsn.com wrote in message ... 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) -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |