Formula being changed

[David Klassen]

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?!

 

[Bob Phillips]

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]

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

 

[Guest]

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

Jerry

 

[David Klassen]

Bingo! Thank you!!

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

Jerry