Sample variance formula that can be dragged down

Hi,

This may be obvious to others, but I truly cannot understand what you are doing. Do the A's and B's in the real worksheet represent numbers? I'm just not following your example.

abousetta

Originally Posted by abousetta

Hi,

This may be obvious to others, but I truly cannot understand what you are doing. Do the A's and B's in the real worksheet represent numbers? I'm just not following your example.

abousetta

so what i am doing is i want to take the variance of the numbers in row 1. however i am dividing the variance into two parts. for every row there are different A's and different B's. I want to take the variance of the numbers in the same column as the A's and the variance of the numbers in the same column as the B's. let me know if its still confusing

I still don't undestand. I honestly don't see a pattern here. How can you drag the formula and ask Excel to recognize a pattern if it changes fom row to row randomly. Now if you mean that the data will be somewhere else and then the A's and B's are labels for the actual data then this is another story, but what is the pattern. I am sorry but I still don't understand what you are trying to accomplish.

abousetta

Originally Posted by abousetta

I still don't undestand. I honestly don't see a pattern here. How can you drag the formula and ask Excel to recognize a pattern if it changes fom row to row randomly. Now if you mean that the data will be somewhere else and then the A's and B's are labels for the actual data then this is another story, but what is the pattern. I am sorry but I still don't understand what you are trying to accomplish.

abousetta

values in the far right column are examples of what i want tocalculate. what i want to do is find some way to say if the first cell in a row is A include the number at the top of the column in the variance calculation, if its not A then do not include it in the calculation.

OK. Now I can see a pattern. Sorry about not understanding what you were after. Let's see if I can find a solution before anyone else.

abousetta

Would something like this work for you:

Please Login or Register  to view this content.

abousetta

I would need the formula to extend to G so from your example it would be extended to

Please Login or Register  to view this content.

However this returns a #VALUE! error....i think because we get some of the " " in the computation and I do not believe the var.s formula recognizes that.

I apologize. I admit that my formula was not correct in all circumstances after further testing. Even so, this should work (attachment)

Please Login or Register  to view this content.

Good luck.

abousetta