I have just worked out that a formula I have been using is not working as I
thought. I am trying to 1) count a number of occurances of a certain risk
appearing, and 2) average them. I filter the data on Sheet1 and Sheet2 is a
reporting page where I do the calculations. I have tried:

Subtotal(1,$AA$5:$AA$992) -for average and subtotal(3,$AA$5:$AA$992) for
count.
AND
SUMPRODUCT(SUBTOTAL(1,OFFSET(Sheet1!$AA$5:$AA$992, ROW(Sheet1!$AA$5:$AA$992)-MIN(ROW(Sheet1!$AA$5:$AA$992)),,1)),--(???)

The question mark is where I come into diffculty. What do I say it equals? I
filter on column A which is a concatenate formula based on month and year.
Each time the report is retrieved it is for different months/years. Because I
made it for others to use, I can't just alter the formula each time.

Could someone help - quickly!!
Thanks

In the last formula what are you trying to calculate.

May an example would help.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gai" wrote:

I have just worked out that a formula I have been using is not working as I
thought. I am trying to 1) count a number of occurances of a certain risk
appearing, and 2) average them. I filter the data on Sheet1 and Sheet2 is a
reporting page where I do the calculations. I have tried:

Subtotal(1,$AA$5:$AA$992) -for average and subtotal(3,$AA$5:$AA$992) for
count.
AND
SUMPRODUCT(SUBTOTAL(1,OFFSET(Sheet1!$AA$5:$AA$992, ROW(Sheet1!$AA$5:$AA$992)-MIN(ROW(Sheet1!$AA$5:$AA$992)),,1)),--(???)

The question mark is where I come into diffculty. What do I say it equals? I
filter on column A which is a concatenate formula based on month and year.
Each time the report is retrieved it is for different months/years. Because I
made it for others to use, I can't just alter the formula each time.

Could someone help - quickly!!
Thanks

Ok here goes.

Column A Column B Column AA
112006 15/11/2006 5
112006 11/12/2006 5
122006 1/12/2006 4
122006 2/12/2006 5
122006 12/12/2006 2
122006 12/12/2006 1
122006 15/12/2006 3
012007 01/01/2007 4
012007 01/01/2007 2

Column A is a formula basically = Column B (but I show it just as monthyear
to filter on- sorry I did say this was a concatenate formula)

I tried a sumtotal formula thinking that that worked on filtered items but
it isn't working. I just tried it again, and it gave me a number <1. Data in
column AA does not yet reach 992, but the filter should filter out all the
unused columns, so I don't know why the fraction (it didn't give me this
result last time).

I tried sumproduct with subtotal as described below but kept getting
confused how it might apply.

What I need is that whatever it filters on, subtotal formula will average
(or count )depending on which I choose.

Is that clearer?? I hope I have explained myself better.

--
Gai


"Martin Fishlock" wrote:

In the last formula what are you trying to calculate.

May an example would help.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gai" wrote:

I have just worked out that a formula I have been using is not working as I
thought. I am trying to 1) count a number of occurances of a certain risk
appearing, and 2) average them. I filter the data on Sheet1 and Sheet2 is a
reporting page where I do the calculations. I have tried:

Subtotal(1,$AA$5:$AA$992) -for average and subtotal(3,$AA$5:$AA$992) for
count.
AND
SUMPRODUCT(SUBTOTAL(1,OFFSET(Sheet1!$AA$5:$AA$992, ROW(Sheet1!$AA$5:$AA$992)-MIN(ROW(Sheet1!$AA$5:$AA$992)),,1)),--(???)

The question mark is where I come into diffculty. What do I say it equals? I
filter on column A which is a concatenate formula based on month and year.
Each time the report is retrieved it is for different months/years. Because I
made it for others to use, I can't just alter the formula each time.

Could someone help - quickly!!
Thanks

Sorry Martin. I found the problem- it was with the database itself. Because I
was only testing the formula, I did not complete all columns in the data set,
so it therefore didn't see it as a continuation of the data. When I completed
all columns and therefore had completed data, I found the Subtotal formula
worked. Sorry to have wasted your time. THanks for your help. I'll try to
hide my blonde a bit better next time.

Regards from Australia

Gai


"Martin Fishlock" wrote:

In the last formula what are you trying to calculate.

May an example would help.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gai" wrote:

I have just worked out that a formula I have been using is not working as I
thought. I am trying to 1) count a number of occurances of a certain risk
appearing, and 2) average them. I filter the data on Sheet1 and Sheet2 is a
reporting page where I do the calculations. I have tried:

Subtotal(1,$AA$5:$AA$992) -for average and subtotal(3,$AA$5:$AA$992) for
count.
AND
SUMPRODUCT(SUBTOTAL(1,OFFSET(Sheet1!$AA$5:$AA$992, ROW(Sheet1!$AA$5:$AA$992)-MIN(ROW(Sheet1!$AA$5:$AA$992)),,1)),--(???)

The question mark is where I come into diffculty. What do I say it equals? I
filter on column A which is a concatenate formula based on month and year.
Each time the report is retrieved it is for different months/years. Because I
made it for others to use, I can't just alter the formula each time.

Could someone help - quickly!!
Thanks