Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula helper gives correct answer but the cell content is differ | Excel Discussion (Misc queries) | |||
Won't give correct answer | Excel Worksheet Functions | |||
Fill down produces correct formula but wrong answer | Excel Worksheet Functions | |||
Formula correct, answer wrong | Excel Discussion (Misc queries) | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |