Referencing range with column heading

[Twee]

I am trying to calculate the average age of a group of patients under the column heading of Age. Is there anyway to calculate it as to point out the column heading title instead of the referencing the column range reference. Also, the age range can vary as well (meaning, sometimes, there's 30 rows and sometimes 45 rows)and I am sure I have to use the offset function; I'm just not sure how to incorporate it.


For instance: I want to change this: =COUNTIF(Data!H2:H35,">=65")& " patients over 65"


In the formula H2:H35 are under the column of age and as new data columns get added, it might shift so I don't want to have to go back to check the reference again if it a new column gets added.


Thanks for any help anyone can offer.

 

[Hui]

Twee


Try something like


=+SUMIF(H2:H200,">=65")/COUNTIF(H2:H200,">=65")

 

[Twee]

Thanks, Hui for chiming in to help. My problem isn't so much the calculation, it's the fact that the Age column could move and might change reference from H2:H200 to another K2:K200. I just want to make sure i reference the column heading if possible because new columns are always added.


i hope that makes a bit more sense. Any ideas you have would be much appreciated.

 

[razaas]

Twee


With the Tables feature in Excel 2007 and above you can do that. Convert your data into table then you can use column heading in any of your formula. The format is like this:

=COUNTIF([ColumnHeading],Criteria) if within same table

=COUNTIF(TableNumber[ColumnHeading],Criteria) in other table or sheet

 

[Twee]

Hi razaas,


Although I don't have Excel 2007, I remember now how to do it. It's all in the Defininig name. For instance, rather than sum(a1:a10), you can name the area quantity and put sum(quantity). Thanks for prompting the idea.

 

[Hui]

Twee


Another way I like is the use of named ranges


To do what you want try setting up 2 named Ranges

Coll =MATCH("Age",Sheet1!$A$1:$Z$1,0)

Age =OFFSET(Sheet1!$A$1,1,Coll-1,COUNTA(OFFSET(Sheet1!$A$1:$A$100,0,Coll-1))-1,1)


Coll is the column number of the "Age" Column

Age is a Range Which is offset from A1 across to the Age Column and Counts the number of entries in the column

The above assumes that Row 1 has the field names you want to search


Once setup you can put a formula

=+sum(Age) or =+Average(Age)


Make sure there is only 1 column named Age

Also make sure there is no gaps in the Age column

 

[Hui]

...

and then change column names and it will adjust accordingly

 

[SAMBASIVA RAO]

Select the Range or column which you want to count

In the name box (It is just left to formula bar) define the range you selected, like "Age" and press enter


The Use the Formula =countif(age,">=65")