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?

the key combo is ctrl+shift+enter. Try this
=average(if(a2:a19="3/0",m2:m19))


--
Don Guillett
SalesAid Software

"SS" wrote in message
...
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

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