Formula being changed

I'm using Excel X (on Mac) to do some survey statistics. Each row
contains a question and the following six columns contain the number of
responses for each of A, B, C, D, E and N/A. The next column contains
a formula to calculate a weighted average of the responses, where A
counts as 5 points, B as 4 points, etc. I want to leave any N/A
responses out of the calculations so my formula is:

=(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11)

As I get new surveys to compile, I just clear out the old responses (in
C11:H41) and then start typing in the new counts. The really annoying
thing is, if I enter a number into the H column (to track the N/A
responses) Excel will automatically edit my formula (in column I) to:

=(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11+H11)

My question: Why is it doing that?!?! And how can I get it to stop?!

Don't know why, but your formula can be simplified to

=SUMPRODUCT(C11:G11,{5,4,3,2,1})/SUM(C11:G11)

which might also stop the problem (maybe!)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"David Klassen" <klassen@rowan.edu> wrote in message
news:1147962235.446642.3310@j73g2000cwa.googlegroups.com...
> I'm using Excel X (on Mac) to do some survey statistics. Each row
> contains a question and the following six columns contain the number of
> responses for each of A, B, C, D, E and N/A. The next column contains
> a formula to calculate a weighted average of the responses, where A
> counts as 5 points, B as 4 points, etc. I want to leave any N/A
> responses out of the calculations so my formula is:
>
> =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11)
>
> As I get new surveys to compile, I just clear out the old responses (in
> C11:H41) and then start typing in the new counts. The really annoying
> thing is, if I enter a number into the H column (to track the N/A
> responses) Excel will automatically edit my formula (in column I) to:
>
> =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11+H11)
>
> My question: Why is it doing that?!?! And how can I get it to stop?!
>

Cool funtion! I've never heard of it.

Tired it, but now it does the "auto correction" to
=SUMPRODUCT(C11:G11,{5,4,3,2,1})/SUM(C11:H11)

Argh!

Thanks for the new function though!

- Dave

Bob Phillips wrote:
> Don't know why, but your formula can be simplified to
>
> =SUMPRODUCT(C11:G11,{5,4,3,2,1})/SUM(C11:G11)
>
> which might also stop the problem (maybe!)
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "David Klassen" <klassen@rowan.edu> wrote in message
> news:1147962235.446642.3310@j73g2000cwa.googlegroups.com...
> > I'm using Excel X (on Mac) to do some survey statistics. Each row
> > contains a question and the following six columns contain the number of
> > responses for each of A, B, C, D, E and N/A. The next column contains
> > a formula to calculate a weighted average of the responses, where A
> > counts as 5 points, B as 4 points, etc. I want to leave any N/A
> > responses out of the calculations so my formula is:
> >
> > =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11)
> >
> > As I get new surveys to compile, I just clear out the old responses (in
> > C11:H41) and then start typing in the new counts. The really annoying
> > thing is, if I enter a number into the H column (to track the N/A
> > responses) Excel will automatically edit my formula (in column I) to:
> >
> > =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11+H11)
> >
> > My question: Why is it doing that?!?! And how can I get it to stop?!
> >

Tools|Options|Edit and uncheck "Extend list formats and formulas"

Jerry

"David Klassen" wrote:

> I'm using Excel X (on Mac) to do some survey statistics. Each row
> contains a question and the following six columns contain the number of
> responses for each of A, B, C, D, E and N/A. The next column contains
> a formula to calculate a weighted average of the responses, where A
> counts as 5 points, B as 4 points, etc. I want to leave any N/A
> responses out of the calculations so my formula is:
>
> =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11)
>
> As I get new surveys to compile, I just clear out the old responses (in
> C11:H41) and then start typing in the new counts. The really annoying
> thing is, if I enter a number into the H column (to track the N/A
> responses) Excel will automatically edit my formula (in column I) to:
>
> =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11+H11)
>
> My question: Why is it doing that?!?! And how can I get it to stop?!
>
>

Bingo! Thank you!!

Jerry W. Lewis wrote:
> Tools|Options|Edit and uncheck "Extend list formats and formulas"
>
> Jerry
>
> "David Klassen" wrote:
>
> > I'm using Excel X (on Mac) to do some survey statistics. Each row
> > contains a question and the following six columns contain the number of
> > responses for each of A, B, C, D, E and N/A. The next column contains
> > a formula to calculate a weighted average of the responses, where A
> > counts as 5 points, B as 4 points, etc. I want to leave any N/A
> > responses out of the calculations so my formula is:
> >
> > =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11)
> >
> > As I get new surveys to compile, I just clear out the old responses (in
> > C11:H41) and then start typing in the new counts. The really annoying
> > thing is, if I enter a number into the H column (to track the N/A
> > responses) Excel will automatically edit my formula (in column I) to:
> >
> > =(C11*5+D11*4+E11*3+F11*2+G11*1)/(C11+D11+E11+F11+G11+H11)
> >
> > My question: Why is it doing that?!?! And how can I get it to stop?!
> >
> >