How to subtract a series from another with summing

[laut]

Here is a stumper, at least it has stumped me this far, below in E2 is the formula:

=((SUM(B2:B6)/COUNTA(A2:A6))(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUM(D2:D6))(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6))))

I can't figure out how to replace the "SUM(D2:D6)" part of that formula so that I can completely get rid of column C and D. The bounty is my appreciation and my respect for your Excel skills. Thanks in advance.

 

[NBVC]

Try replacing with:

SUMPRODUCT(--(B2:B6-A2:A6)^2)

 

[Jonmo1]

Welcome to the board..

Your post doesn't really make sense, the formula you posted is not valid.
It has a coule instances of )( which is invalid syntax, I presume it must be )*( or something..
Can you repost a complete formula that actually compiles in your sheet?

=((SUM(B2:B6)/COUNTA(A2:A6))??(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUM(D2:D6))??(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6))))


Also try using the Excel Jeanie to post a more useable representation of your data.
See my signature for a link to the Jeanie.

 

[laut]

Sorry about that, try this:

=((SUM(B2:B6)/COUNTA(A2:A6))-(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUM(D2:D6))-(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6))))

Welcome to the board..

Your post doesn't really make sense, the formula you posted is not valid.
It has a coule instances of )( which is invalid syntax, I presume it must be )*( or something..
Can you repost a complete formula that actually compiles in your sheet?

=((SUM(B2:B6)/COUNTA(A2:A6))??(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUM(D2:D6))??(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6))))


Also try using the Excel Jeanie to post a more useable representation of your data.
See my signature for a link to the Jeanie.

 

[NBVC]

Have you tried the replacement I suggested? Does it work?

 

[laut]

@NBVC
It doesn't work unfortunately, the formulas give two different answers.

Original:
=((SUM(B2:B6)/COUNTA(A2:A6))-(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUM(D2:D6))-(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6))))

=6.532


Your suggested modification:
=((SUM(B2:B6)/COUNTA(A2:A6))-(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUMPRODUCT((B2:B6-A2:A6)^2)))-(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6)))

= 0.4

Have you tried the replacement I suggested? Does it work?

 

[tusharm]

What are you trying to do? In words, not with a formula? It looks like the coefficient of correlation or something similar, though there are so many parenthesis that I can't tell if it is correct or not! {grin}


SUM(B)/COUNTA(A)-SUM(A)/COUNTA(A) can be reduced to (SUM(B)-SUM(A))/COUNTA(A)

Sorry about that, try this:

=((SUM(B2:B6)/COUNTA(A2:A6))-(SUM(A2:A6)/COUNTA(A2:A6)))/((SQRT((1/(COUNTA(A2:A6)-1))*((SUM(D2:D6))-(((SUM(B2:B6)-SUM(A2:A6))^2)/COUNTA(B2:B6)))))/(SQRT(COUNTA(B2:B6))))