Prev: Locking content, formula, and format in a cell
Next: Can you freeze in two directions in excel
From: Fuad on 13 May 2010 13:36
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
From: Jim Thomlinson on 13 May 2010 14:01
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
 | 
Pages: 1
Prev: Locking content, formula, and format in a cell
Next: Can you freeze in two directions in excel