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