For this you can use a dynamic named range. Do you need help with this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again for
your help guys.


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670

Assuming that Sheet1, Column B, starting at B2, contains the data, try
the following...

1) Define the following dynamic named range:

Insert Name Define

Name: Values

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Ok

Change the references accordingly.

2) Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=AVERAGE(IF(ROW(Values)=LARGE(IF(Values,ROW(Value s)),30),IF(Values,Value
s)))

Hope this helps!

In article ,
gimiv wrote:

For this you can use a dynamic named range. Do you need help with this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again for
your help guys.

Note to the OP:

If you have less than 30 values < 0 you'll get a #NUM! error.

Biff

"Domenic" wrote in message
...
Assuming that Sheet1, Column B, starting at B2, contains the data, try
the following...

1) Define the following dynamic named range:

Insert Name Define

Name: Values

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Ok

Change the references accordingly.

2) Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=AVERAGE(IF(ROW(Values)=LARGE(IF(Values,ROW(Value s)),30),IF(Values,Value
s)))

Hope this helps!

In article ,
gimiv wrote:

For this you can use a dynamic named range. Do you need help with this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again for
your help guys.

Thanks Biff! Where do I send my cheque... <VBG

In article ,
"Biff" wrote:

Note to the OP:

If you have less than 30 values < 0 you'll get a #NUM! error.

Biff

Biff Wrote:
Note to the OP:

If you have less than 30 values < 0 you'll get a #NUM! error.

Biff

"Domenic" wrote in message
...
Assuming that Sheet1, Column B, starting at B2, contains the data,
try
the following...

1) Define the following dynamic named range:

Insert Name Define

Name: Values

Refers to:


=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(9.99 999999999999E+307,Sheet
1!$B$2:$B$65536))

Click Ok

Change the references accordingly.

2) Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...


=AVERAGE(IF(ROW(Values)=LARGE(IF(Values,ROW(Value s)),30),IF(Values,Value
s)))

Hope this helps!

In article ,
gimiv wrote:

For this you can use a dynamic named range. Do you need help with
this?

Inserting it into an OFFSET in your equation? yes. = ) thanks again
for
your help guys.

Wow, this worked perfectly. Hate to be a pain, but can you explain how
you went about the logic to achieve this statement or does that just
come with years and years of experience. I mean, to be able to identify
the problem and match it to the right complex formula?


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php...o&userid=35726
View this thread: http://www.excelforum.com/showthread...hreadid=558670

In article ,
gimiv wrote:

Wow, this worked perfectly. Hate to be a pain, but can you explain how
you went about the logic to achieve this statement or does that just
come with years and years of experience. I mean, to be able to identify
the problem and match it to the right complex formula?

Basically, I watch and learn from others who are more experienced. It's
amazing what one can learn by frequenting these newsgroups, forums,
etc...