|
{} SumIf and CountIf
I am trying to use the following formulas
=sumif(A2:A19,"3/0",M2:M19)/countif(A2:A19,"3/0") =SUMIF(A2:A19,0,M2:M19)/COUNTIF(A2:A19,0) columns A and M look as follows: Size Post/Laser 0 1.112 0 1.099 0 1.105 0 1.111 0 1.112 0 1.116 0 1.116 3/0 1.109 3/0 1.105 3/0 1.097 3/0 1.094 3/0 1.106 3/0 1.112 3/0 1.117 3/0 1.094 3/0 1.094 3/0 1.107 3/0 1.090 In one case, the formula works beautifully. in the other case, the cell spits back the formula. It does not give me the result. I seem to remember having this problem before and pressing some combination of keys to get "{}" to appear on either end of the formula and it working then, but I do not remember the key combo. Can anyone help? Why does this happen? Thanks SS |
You have the cell formatted as Text, so XL doesn't parse the entry as a
formula. Format the cell as General and reenter the formula. In article , "SS" wrote: In one case, the formula works beautifully. in the other case, the cell spits back the formula. It does not give me the result. I seem to remember having this problem before and pressing some combination of keys to get "{}" to appear on either end of the formula and it working then, but I do not remember the key combo. Can anyone help? Why does this happen? |
Most likely you have formatted the cell with the formula as text or a leading
space or something, select the cell, press F2 and enter. Array entering (to get curly braces) has anything to do with this, you can also try to replace = with = to trigger caculation, your formula looks OK Regards, Peo Sjoblom "SS" wrote: I am trying to use the following formulas =sumif(A2:A19,"3/0",M2:M19)/countif(A2:A19,"3/0") =SUMIF(A2:A19,0,M2:M19)/COUNTIF(A2:A19,0) columns A and M look as follows: Size Post/Laser 0 1.112 0 1.099 0 1.105 0 1.111 0 1.112 0 1.116 0 1.116 3/0 1.109 3/0 1.105 3/0 1.097 3/0 1.094 3/0 1.106 3/0 1.112 3/0 1.117 3/0 1.094 3/0 1.094 3/0 1.107 3/0 1.090 In one case, the formula works beautifully. in the other case, the cell spits back the formula. It does not give me the result. I seem to remember having this problem before and pressing some combination of keys to get "{}" to appear on either end of the formula and it working then, but I do not remember the key combo. Can anyone help? Why does this happen? Thanks SS |
Brain Freeze on my part. Thanks!
"JE McGimpsey" wrote: You have the cell formatted as Text, so XL doesn't parse the entry as a formula. Format the cell as General and reenter the formula. In article , "SS" wrote: In one case, the formula works beautifully. in the other case, the cell spits back the formula. It does not give me the result. I seem to remember having this problem before and pressing some combination of keys to get "{}" to appear on either end of the formula and it working then, but I do not remember the key combo. Can anyone help? Why does this happen? |
Check what your second equation is formated as. If it is coming back as the
equation. the cell may be formated as text. if so reformat it as general A second thing to try is to try in your second equation =SUMIF(A2:A19,0,M2:M19)/COUNTIF(A2:A19,"0") If your 0 is a text zero rather than o number you would get an error. "SS" wrote: I am trying to use the following formulas =sumif(A2:A19,"3/0",M2:M19)/countif(A2:A19,"3/0") =SUMIF(A2:A19,0,M2:M19)/COUNTIF(A2:A19,0) columns A and M look as follows: Size Post/Laser 0 1.112 0 1.099 0 1.105 0 1.111 0 1.112 0 1.116 0 1.116 3/0 1.109 3/0 1.105 3/0 1.097 3/0 1.094 3/0 1.106 3/0 1.112 3/0 1.117 3/0 1.094 3/0 1.094 3/0 1.107 3/0 1.090 In one case, the formula works beautifully. in the other case, the cell spits back the formula. It does not give me the result. I seem to remember having this problem before and pressing some combination of keys to get "{}" to appear on either end of the formula and it working then, but I do not remember the key combo. Can anyone help? Why does this happen? Thanks SS |
| All times are GMT +1. The time now is 06:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com