Excel formula not working with reference cells

[rjplante]

I have a large data set with several columns of data each of which is several thousand rows long. The data is exported from another machine that dumps the data into this spreadsheet. So my original data set is formatted as general. I have a formula that reads as =Average(K:K) and generates the average for the entire column. I would like to have two cells each with a row number in it and have the formula use those numbers as the rows to use in the calculation. The formula I have written is =AVERAGE("K" & X11 & ":K" & X12 ), where X11 = 2500 and X12 = 3750. When I hit enter I get a #Value error in the cell. When I select the cell with the error in it, and mouse over the drop down arrow this error description is displayed, "A value used in the formula is of the wrong data type." I went to the data table and changed the data in column K from General to Number. I also made sure to format the data in cells X11 and X12 to number as well. None of which fixed my error. When I go to the cell with the error and select the "Show Calculation Steps" item it shows me the formula AVERAGE("K2500:K3750") and states that the next evaluation will result in an error.

What am I doing wrong with the formula?

 

[Fluff]

You cannot concatenate ranges like that.
Try

=AVERAGE(INDEX(K:K,X11):INDEX(K:K,X12 ))

 

[rjplante]

It seems to be working. I will test it out a little more to verify it is only capturing my desired data set.

 

[Fluff]

Ok

 

[rjplante]

Testing looks great. Thanks Fluff. I really appreciate the help.

 

[Fluff]

You're welcome & thanks for the feedback.