Array formula correct but end up with 0 for answer
 

I have a lengthy array formula that is giving me 0 for an answer which I know
is incorrect (it should be 384...). I evaluated the formula and it comes up
with 384... and then on the last click it changes it to 0. I don't
understand why. It worked my other spreadsheet.

Array formula correct but end up with 0 for answer
 

Did you commit it with CTRL-SHIFT-ENTER ?

Can you post it here with an explanation of the terms/data used ?

Pete



On Jul 28, 1:38 am, Heather wrote:
I have a lengthy array formula that is giving me 0 for an answer which I know
is incorrect (it should be 384...). I evaluated the formula and it comes up
with 384... and then on the last click it changes it to 0. I don't
understand why. It worked my other spreadsheet.

Array formula correct but end up with 0 for answer
 

Yes, I do CTRL+SHIFT+ENTER for an array formula. Thanks for any help you can
give me.

like I said, when I evaluate the formula, it gives the answer 384...without
any parenthesis. Then I click one more time and it gives me 0.

the answer goes in b26

here's the formula

=(B$4*((B7*(MIN(IF($K7:$N7<0,$K7:$N7))))+(B8*(MIN (IF($K8:$N8<0,$K8:$N8))))+(B9*(MIN(IF($K9:$N9<0, $K9:$N9))))+(B10*(MIN(IF($K10:$N10<0,$K10:$N10))) )+(B11*(MIN(IF($K11:$N11<0,$K11:$N11))))+(B12*(MI N(IF($K12:$N12<0,$K12:$N12))))+(B13*(MIN(IF($K13: $N13<0,$K13:$N13))))+(B14*(MIN(IF($K14:$N14<0,$K 14:$N14))))+(B15*(MIN(IF($K15:$N15<0,$K15:$N15))) )+(B16*(MIN(IF($K16:$N16<0,$K16:$N16))))+(B17*(MI N(IF($K17:$N17<0,$K17:$N17))))+(B18*(MIN(IF($K18: $N18<0,$K18:$N18))))+(B19*(MIN(IF($K19:$N19<0,$K 19:$N19))))+(B20*(MIN(IF($K20:$N20<0,$K20:$N20))) )+(B21*(MIN(IF($K21:$N21<0,$K21:$N21))))+(B22*(MI N(IF($K22:$N22<0,$K22:$N22))))+(B23*(MIN(IF($K23: $N23<0,$K23:$N23))))+(B24*(MIN(IF($K24:$N24<0,$K 24:$N24))))+(B25*(MIN(IF($K25:$N25<0,$K25:$N25))) )))

b4 45
b7 10
b10 1
b12 1
b13 1
b15 1
b25 1

rows 7-25
k l m n

0.04 0.00 0.05 0.05
0.17 0.83 0.22 0.21
1.36 0.79 0.04 0.59
0.82 0.69 0.70 0.65
1.97 1.32 1.31 1.25
0.20 1.68 1.49 1.49
2.84 2.57 3.16 2.49
2.84 2.57 3.16 0.00
0.00 2.57 0.00 2.39
0.88 1.92 2.10 1.95
1.74 0.51 0.22 0.27
2.24 1.83 2.46 1.79
0.05 0.83 0.08 0.08
0.47 0.68 0.18 0.48
0.47 0.68 0.43 0.48
3.97 4.19 2.62 3.29
0.86 0.71 0.51 0.55
0.97 0.23 0.32 0.29
0.84 0.72 1.30 1.79



"Pete_UK" wrote:

Did you commit it with CTRL-SHIFT-ENTER ?

Can you post it here with an explanation of the terms/data used ?

Pete



On Jul 28, 1:38 am, Heather wrote:
I have a lengthy array formula that is giving me 0 for an answer which I know
is incorrect (it should be 384...). I evaluated the formula and it comes up
with 384... and then on the last click it changes it to 0. I don't
understand why. It worked my other spreadsheet.

Array formula correct but end up with 0 for answer
 

I have the same thing in another sheet. I did ROUNDUP(g7/d7,0), pulled it
down the column and got the correct answers. then when I wanted to do the
same formula (same conditions) in yet another sheet, it gives me 0 for an
answer. Any clue what I'm doing wrong?

"Heather" wrote:

Yes, I do CTRL+SHIFT+ENTER for an array formula. Thanks for any help you can
give me.

like I said, when I evaluate the formula, it gives the answer 384...without
any parenthesis. Then I click one more time and it gives me 0.

the answer goes in b26

here's the formula

=(B$4*((B7*(MIN(IF($K7:$N7<0,$K7:$N7))))+(B8*(MIN (IF($K8:$N8<0,$K8:$N8))))+(B9*(MIN(IF($K9:$N9<0, $K9:$N9))))+(B10*(MIN(IF($K10:$N10<0,$K10:$N10))) )+(B11*(MIN(IF($K11:$N11<0,$K11:$N11))))+(B12*(MI N(IF($K12:$N12<0,$K12:$N12))))+(B13*(MIN(IF($K13: $N13<0,$K13:$N13))))+(B14*(MIN(IF($K14:$N14<0,$K 14:$N14))))+(B15*(MIN(IF($K15:$N15<0,$K15:$N15))) )+(B16*(MIN(IF($K16:$N16<0,$K16:$N16))))+(B17*(MI N(IF($K17:$N17<0,$K17:$N17))))+(B18*(MIN(IF($K18: $N18<0,$K18:$N18))))+(B19*(MIN(IF($K19:$N19<0,$K 19:$N19))))+(B20*(MIN(IF($K20:$N20<0,$K20:$N20))) )+(B21*(MIN(IF($K21:$N21<0,$K21:$N21))))+(B22*(MI N(IF($K22:$N22<0,$K22:$N22))))+(B23*(MIN(IF($K23: $N23<0,$K23:$N23))))+(B24*(MIN(IF($K24:$N24<0,$K 24:$N24))))+(B25*(MIN(IF($K25:$N25<0,$K25:$N25))) )))

b4 45
b7 10
b10 1
b12 1
b13 1
b15 1
b25 1

rows 7-25
k l m n

0.04 0.00 0.05 0.05
0.17 0.83 0.22 0.21
1.36 0.79 0.04 0.59
0.82 0.69 0.70 0.65
1.97 1.32 1.31 1.25
0.20 1.68 1.49 1.49
2.84 2.57 3.16 2.49
2.84 2.57 3.16 0.00
0.00 2.57 0.00 2.39
0.88 1.92 2.10 1.95
1.74 0.51 0.22 0.27
2.24 1.83 2.46 1.79
0.05 0.83 0.08 0.08
0.47 0.68 0.18 0.48
0.47 0.68 0.43 0.48
3.97 4.19 2.62 3.29
0.86 0.71 0.51 0.55
0.97 0.23 0.32 0.29
0.84 0.72 1.30 1.79



"Pete_UK" wrote:

Did you commit it with CTRL-SHIFT-ENTER ?

Can you post it here with an explanation of the terms/data used ?

Pete



On Jul 28, 1:38 am, Heather wrote:
I have a lengthy array formula that is giving me 0 for an answer which I know
is incorrect (it should be 384...). I evaluated the formula and it comes up
with 384... and then on the last click it changes it to 0. I don't
understand why. It worked my other spreadsheet.

Array formula correct but end up with 0 for answer
 

I built the data table from your posted data....and did not get either of the
results you posted.

The array formula I used is an abbreviated version of yours:
B26:
=B$4*(
B7*MIN(IF($K7:$N7,$K7:$N7))
+B8*MIN(IF($K8:$N8,$K8:$N8))
+B9*MIN(IF($K9:$N9,$K9:$N9))
+B10*MIN(IF($K10:$N10,$K10:$N10))
+B11*MIN(IF($K11:$N11,$K11:$N11))
+B12*MIN(IF($K12:$N12,$K12:$N12))
+B13*MIN(IF($K13:$N13,$K13:$N13))
+B14*MIN(IF($K14:$N14,$K14:$N14))
+B15*MIN(IF($K15:$N15,$K15:$N15))
+B16*MIN(IF($K16:$N16,$K16:$N16))
+B17*MIN(IF($K17:$N17,$K17:$N17))
+B18*MIN(IF($K18:$N18,$K18:$N18))
+B19*MIN(IF($K19:$N19,$K19:$N19))
+B20*MIN(IF($K20:$N20,$K20:$N20))
+B21*MIN(IF($K21:$N21,$K21:$N21))
+B22*MIN(IF($K22:$N22,$K22:$N22))
+B23*MIN(IF($K23:$N23,$K23:$N23))
+B24*MIN(IF($K24:$N24,$K24:$N24))
+B25*MIN(IF($K25:$N25,$K25:$N25))
)

....Yet, both versions return 308.25

Try for yourself in a new sheet and see if you get those results. If yes,
then the problem lies in the original data.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Heather" wrote:

Yes, I do CTRL+SHIFT+ENTER for an array formula. Thanks for any help you can
give me.

like I said, when I evaluate the formula, it gives the answer 384...without
any parenthesis. Then I click one more time and it gives me 0.

the answer goes in b26

here's the formula

=(B$4*((B7*(MIN(IF($K7:$N7<0,$K7:$N7))))+(B8*(MIN (IF($K8:$N8<0,$K8:$N8))))+(B9*(MIN(IF($K9:$N9<0, $K9:$N9))))+(B10*(MIN(IF($K10:$N10<0,$K10:$N10))) )+(B11*(MIN(IF($K11:$N11<0,$K11:$N11))))+(B12*(MI N(IF($K12:$N12<0,$K12:$N12))))+(B13*(MIN(IF($K13: $N13<0,$K13:$N13))))+(B14*(MIN(IF($K14:$N14<0,$K 14:$N14))))+(B15*(MIN(IF($K15:$N15<0,$K15:$N15))) )+(B16*(MIN(IF($K16:$N16<0,$K16:$N16))))+(B17*(MI N(IF($K17:$N17<0,$K17:$N17))))+(B18*(MIN(IF($K18: $N18<0,$K18:$N18))))+(B19*(MIN(IF($K19:$N19<0,$K 19:$N19))))+(B20*(MIN(IF($K20:$N20<0,$K20:$N20))) )+(B21*(MIN(IF($K21:$N21<0,$K21:$N21))))+(B22*(MI N(IF($K22:$N22<0,$K22:$N22))))+(B23*(MIN(IF($K23: $N23<0,$K23:$N23))))+(B24*(MIN(IF($K24:$N24<0,$K 24:$N24))))+(B25*(MIN(IF($K25:$N25<0,$K25:$N25))) )))

b4 45
b7 10
b10 1
b12 1
b13 1
b15 1
b25 1

rows 7-25
k l m n

0.04 0.00 0.05 0.05
0.17 0.83 0.22 0.21
1.36 0.79 0.04 0.59
0.82 0.69 0.70 0.65
1.97 1.32 1.31 1.25
0.20 1.68 1.49 1.49
2.84 2.57 3.16 2.49
2.84 2.57 3.16 0.00
0.00 2.57 0.00 2.39
0.88 1.92 2.10 1.95
1.74 0.51 0.22 0.27
2.24 1.83 2.46 1.79
0.05 0.83 0.08 0.08
0.47 0.68 0.18 0.48
0.47 0.68 0.43 0.48
3.97 4.19 2.62 3.29
0.86 0.71 0.51 0.55
0.97 0.23 0.32 0.29
0.84 0.72 1.30 1.79



"Pete_UK" wrote:

Did you commit it with CTRL-SHIFT-ENTER ?

Can you post it here with an explanation of the terms/data used ?

Pete



On Jul 28, 1:38 am, Heather wrote:
I have a lengthy array formula that is giving me 0 for an answer which I know
is incorrect (it should be 384...). I evaluated the formula and it comes up
with 384... and then on the last click it changes it to 0. I don't
understand why. It worked my other spreadsheet.

Array formula correct but end up with 0 for answer
 

I would use a helper column to get the min. This will eliminate the need for
that monster array formula. It appears that all your values are positive.

Entered in O7 and copied down to O25:

=IF(SUM(K7:N7),SMALL(K7:N7,1+COUNTIF(K7:N7,0)),0)

Then your formula becomes:

=B4*SUMPRODUCT(B7:B25,O7:O25)

--
Biff
Microsoft Excel MVP


"Heather" wrote in message
...
Yes, I do CTRL+SHIFT+ENTER for an array formula. Thanks for any help you
can
give me.

like I said, when I evaluate the formula, it gives the answer
384...without
any parenthesis. Then I click one more time and it gives me 0.

the answer goes in b26

here's the formula

=(B$4*((B7*(MIN(IF($K7:$N7<0,$K7:$N7))))+(B8*(MIN (IF($K8:$N8<0,$K8:$N8))))+(B9*(MIN(IF($K9:$N9<0, $K9:$N9))))+(B10*(MIN(IF($K10:$N10<0,$K10:$N10))) )+(B11*(MIN(IF($K11:$N11<0,$K11:$N11))))+(B12*(MI N(IF($K12:$N12<0,$K12:$N12))))+(B13*(MIN(IF($K13: $N13<0,$K13:$N13))))+(B14*(MIN(IF($K14:$N14<0,$K 14:$N14))))+(B15*(MIN(IF($K15:$N15<0,$K15:$N15))) )+(B16*(MIN(IF($K16:$N16<0,$K16:$N16))))+(B17*(MI N(IF($K17:$N17<0,$K17:$N17))))+(B18*(MIN(IF($K18: $N18<0,$K18:$N18))))+(B19*(MIN(IF($K19:$N19<0,$K 19:$N19))))+(B20*(MIN(IF($K20:$N20<0,$K20:$N20))) )+(B21*(MIN(IF($K21:$N21<0,$K21:$N21))))+(B22*(MI N(IF($K22:$N22<0,$K22:$N22))))+(B23*(MIN(IF($K23: $N23<0,$K23:$N23))))+(B24*(MIN(IF($K24:$N24<0,$K 24:$N24))))+(B25*(MIN(IF($K25:$N25<0,$K25:$N25))) )))

b4 45
b7 10
b10 1
b12 1
b13 1
b15 1
b25 1

rows 7-25
k l m n

0.04 0.00 0.05 0.05
0.17 0.83 0.22 0.21
1.36 0.79 0.04 0.59
0.82 0.69 0.70 0.65
1.97 1.32 1.31 1.25
0.20 1.68 1.49 1.49
2.84 2.57 3.16 2.49
2.84 2.57 3.16 0.00
0.00 2.57 0.00 2.39
0.88 1.92 2.10 1.95
1.74 0.51 0.22 0.27
2.24 1.83 2.46 1.79
0.05 0.83 0.08 0.08
0.47 0.68 0.18 0.48
0.47 0.68 0.43 0.48
3.97 4.19 2.62 3.29
0.86 0.71 0.51 0.55
0.97 0.23 0.32 0.29
0.84 0.72 1.30 1.79



"Pete_UK" wrote:

Did you commit it with CTRL-SHIFT-ENTER ?

Can you post it here with an explanation of the terms/data used ?

Pete



On Jul 28, 1:38 am, Heather wrote:
I have a lengthy array formula that is giving me 0 for an answer which
I know
is incorrect (it should be 384...). I evaluated the formula and it
comes up
with 384... and then on the last click it changes it to 0. I don't
understand why. It worked my other spreadsheet.