Dynamically reference a cell based on cell content
 

Hi All,

I do not know how below copy-paste will show on the post, but I will try to
depict the question in self-explanatory way, so you will gatch what's my
problem:

I have an Average function to calculate the average for the last 12 months
just before Total Colum - (I have several prior periods, but I want only
averge the last twelve month from current month backward). I can do this by
the help of below formula:

1217.416667 =AVERAGE(OFFSET(Q17,,-1):OFFSET(Q17,,-12,,12))
PER-1 PER-2 PER-3 PER-4 PER-5 PER-6 PER-7 PER-8 PER-9 PER-10 PER-11 PER-12 PER-13 PER-14 TOTAL
4567 1234 12456 1235 124 432 45 234 23 12 12 12 12 12 12

However, I do not want to manually type Cell Q17, R17, S17 depending on
every time I add new period. So what I want is to ask Excel to find the
column/cell reference where word TOTAL stands and counts back.

Does anyone has got a tip...?

Thanks in advance

Regards,

Fuad

Dynamically reference a cell based on cell content
 

I dont' see exactly what you are trying to do but if you want to reference
the word Total you can use the match function that will tell you at what
position you will find the word.

For example =match("Total", A3:AA3, 0)
will find the word total in the Range A3:AA3 and tell the number of cells
to the left of A3 where you matched.
--
HTH...

Jim Thomlinson


"Fuad" wrote:

Hi All,

I do not know how below copy-paste will show on the post, but I will try to
depict the question in self-explanatory way, so you will gatch what's my
problem:

I have an Average function to calculate the average for the last 12 months
just before Total Colum - (I have several prior periods, but I want only
averge the last twelve month from current month backward). I can do this by
the help of below formula:

1217.416667 =AVERAGE(OFFSET(Q17,,-1):OFFSET(Q17,,-12,,12))
PER-1 PER-2 PER-3 PER-4 PER-5 PER-6 PER-7 PER-8 PER-9 PER-10 PER-11 PER-12 PER-13 PER-14 TOTAL
4567 1234 12456 1235 124 432 45 234 23 12 12 12 12 12 12

However, I do not want to manually type Cell Q17, R17, S17 depending on
every time I add new period. So what I want is to ask Excel to find the
column/cell reference where word TOTAL stands and counts back.

Does anyone has got a tip...?

Thanks in advance

Regards,

Fuad

if you are using excel 2007, try averageif function.

all the best