Array formula correct but end up with 0 for answer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Did you commit it with CTRL-SHIFT-ENTER ?

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

Pete
 
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*(MIN(IF($K12:$N12<>0,$K12:$N12))))+(B13*(MIN(IF($K13:$N13<>0,$K13:$N13))))+(B14*(MIN(IF($K14:$N14<>0,$K14:$N14))))+(B15*(MIN(IF($K15:$N15<>0,$K15:$N15))))+(B16*(MIN(IF($K16:$N16<>0,$K16:$N16))))+(B17*(MIN(IF($K17:$N17<>0,$K17:$N17))))+(B18*(MIN(IF($K18:$N18<>0,$K18:$N18))))+(B19*(MIN(IF($K19:$N19<>0,$K19:$N19))))+(B20*(MIN(IF($K20:$N20<>0,$K20:$N20))))+(B21*(MIN(IF($K21:$N21<>0,$K21:$N21))))+(B22*(MIN(IF($K22:$N22<>0,$K22:$N22))))+(B23*(MIN(IF($K23:$N23<>0,$K23:$N23))))+(B24*(MIN(IF($K24:$N24<>0,$K24:$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
 
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?
 
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
 
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)
 
Back
Top