Formuala to calculate from last cell in range containing value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

uplink600 - 10 Mar 2006 11:28 GMT

Hi

Can you please advise the easiest method to achieve the following.

I need a formula to perform a calculation using the last cell in a
range that contains a value. The range would be say A1:A365 and each
day the user will add to the bottom of the range (for each day of the
year) and I need to calculate from this on a rolling basis (basically a
yearly average).

I hope this is easy to understand.

Thanks

VC

Signature

uplink600

Reply to this Message

Bob Phillips - 10 Mar 2006 11:43 GMT

The last value can be found using

=INDEX(A1:A365,MATCH(9.99999999999999E+307,A1:A365))

if you just want the row, just use

=MATCH(9.99999999999999E+307,A1:A365)

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hi
>
[quoted text clipped - 11 lines]
>
> VC

Reply to this Message

Paul B - 10 Mar 2006 11:48 GMT

uplink600, this return the last numeric valve in A1:A365,
=LOOKUP(9.99999999999999E+307,A1:A365)
you may also want to have a look here for more information on finding the
last cell http://www.xldynamic.com/source/xld.LastValue.html

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Hi
>
[quoted text clipped - 11 lines]
>
> VC

Reply to this Message

Damon Longworth - 17 Feb 2007 00:07 GMT

Hello everyone

The 2007 East Coast Excel / Access User Conference will be held at the Trump
Marina Hotel Casino on April 18-20. If you have missed the past events, make
it a point to attend this one. If you have attended any of the past events,
you know the value of the information shared by the experts along with the
fun and camaraderie shared by all.

Attendees will get to hear from experts like Bob Umlas, Jon Peltier, Bernard
Liengme and Access MVP Andy Baron.

Signature

--
Damon Longworth

2007 East Coast Excel / Access User Conference
April 18-20, 2007 - Providing Microsoft Excel training and Microsoft Excel
Classes
Atlantic City, New Jersey
www.ExcelUserConference.com/ECEUC.html

Reply to this Message