Tell a cell how many rows down to reference

Hiya, I'm new here and relatively new to complicated Excel spreadsheets so I was wondering if you could help:

I'm trying to make a spreadsheet that users can input two adjacent lists of numbers (columns A and B) and get a statistical output from those two sets of numbers. Column C returns the difference between each pair of numbers (B1-A1, B2-A2 etc.) and column D returns the mean difference (I'm trying to make a paired t test). The problem is that I want the user to be able to just input their data and not have to edit the reference fields for their data, so I drag the column C formula far down the spreadsheet (further than anyone could ever fill with data) and use conditional formatting to hide all the zeros. But now column D incorporates all those zeros into the mean value (which of course is very wrong).

So my question is, can I get column D to only reference as many rows in column C, as there are rows entered in column A (or B)? Does that make sense?

Thank you!
hrj21

This would reference the cells in col C that have continuous numeric values in col A: C1:INDEX(C:C,COUNT(A:A))

So, for instance, a SUM() of values in col C for the range of numbers appearing in col A would be: =SUM(C1:INDEX(C:C,COUNT(A:A)))

The OP is generating the 0's by using B1-A1 dragged down. I assumed the OP wanted the 0's for some reason because of the effort to hide them with CF instead of using a simple IF() test.

Thanks for the reply. I thought that had solved it, but the wrong average is being returned by column D. I typed: "=AVERAGE(C1:INDEX(C:C, COUNT(A:A)))" for the cell in column D and this is what I get:

A	B	C	D
10	1	9	9.0
9	1	8
14	3	11

when the real value is 9.333. Any thoughts?

Thanks
hrj21

Also yes the zeros are important to have because a zero difference between column B and column A is important if entered by the user.

Because you are starting your formulas in row 3 you have to adjust the formula I gave you to account for that. So it would be:
=AVERAGE(C3:INDEX(C:C, COUNT(A:A)+2))

But, as suggested by FDibbins, you could change your formula in col C to:
=IF(B1="","",A1-B1) and drag down as far as you want. Then you won't have to worry about unwanted 0's appearing before there is a value in col B and the AVERAGE() function will ignore the "" results. So you would just need =AVERAGE(C:C)
That would eliminate the need for CF to hide 0's which you wouldn't want to do when A1-B1=0

Ah that's great! Thank you for all your comments, it now works perfectly. I didn't know you could specify 'cell =""' as a condition in an IF test.

Cheers!
hrj21