[SOLVED] Dividing the last in a series

[jezzica85]

Hi all, I have a spreadsheet like this:
3000
4000
5000
6000

I want to divide the last number by 3000, and only the last number. If I
add another number on after the last one, say 7000, I want to divide that
one, and only that one. Is there a way to do that?
Thanks!

[Toppers]

Is the data always in ascending sequence so you divide the maximum by the
minimum?

=max(A1:A10)/min(A1:A10)

HTH

"jezzica85" wrote:

> Hi all, I have a spreadsheet like this:
> 3000
> 4000
> 5000
> 6000
>
> I want to divide the last number by 3000, and only the last number. If I
> add another number on after the last one, say 7000, I want to divide that
> one, and only that one. Is there a way to do that?
> Thanks!

[jezzica85]

No, the data isn't always in ascending sequence.

"Toppers" wrote:

> Is the data always in ascending sequence so you divide the maximum by the
> minimum?
>
> =max(A1:A10)/min(A1:A10)
>
> HTH
>
> "jezzica85" wrote:
>
> > Hi all, I have a spreadsheet like this:
> > 3000
> > 4000
> > 5000
> > 6000
> >
> > I want to divide the last number by 3000, and only the last number. If I
> > add another number on after the last one, say 7000, I want to divide that
> > one, and only that one. Is there a way to do that?
> > Thanks!

[jezzica85]

For some reason my earlier post didn't show up, no, the data isn't always in
ascending order.

"Toppers" wrote:

> Is the data always in ascending sequence so you divide the maximum by the
> minimum?
>
> =max(A1:A10)/min(A1:A10)
>
> HTH
>
> "jezzica85" wrote:
>
> > Hi all, I have a spreadsheet like this:
> > 3000
> > 4000
> > 5000
> > 6000
> >
> > I want to divide the last number by 3000, and only the last number. If I
> > add another number on after the last one, say 7000, I want to divide that
> > one, and only that one. Is there a way to do that?
> > Thanks!

[Toppers]

One way is to define your data as a dynamic range e.g. List

Insert=>Name=>Define

Enter "List" as name (for example)

In the Refers To box, enter an Offset formula that defines the range size,
based on the number of items in the column, e.g.:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
In this example, the list is on Sheet1, starting in cell A2

For your calculation put this in the required cell:

=OFFSET(A2,ROWS(List)-1,0)/(A2)

This divides last number in range "List" by first

HTH

"jezzica85" wrote:

> Hi all, I have a spreadsheet like this:
> 3000
> 4000
> 5000
> 6000
>
> I want to divide the last number by 3000, and only the last number. If I
> add another number on after the last one, say 7000, I want to divide that
> one, and only that one. Is there a way to do that?
> Thanks!

[Toppers]

An alternative:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))/A2

The INDEX will find the last value.

HTH

"Toppers" wrote:

> One way is to define your data as a dynamic range e.g. List
>
> Insert=>Name=>Define
>
> Enter "List" as name (for example)
>
> In the Refers To box, enter an Offset formula that defines the range size,
> based on the number of items in the column, e.g.:
> =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
> In this example, the list is on Sheet1, starting in cell A2
>
> For your calculation put this in the required cell:
>
> =OFFSET(A2,ROWS(List)-1,0)/(A2)
>
> This divides last number in range "List" by first
>
> HTH
>
> "jezzica85" wrote:
>
> > Hi all, I have a spreadsheet like this:
> > 3000
> > 4000
> > 5000
> > 6000
> >
> > I want to divide the last number by 3000, and only the last number. If I
> > add another number on after the last one, say 7000, I want to divide that
> > one, and only that one. Is there a way to do that?
> > Thanks!

[jezzica85]

Thanks Toppers, the list method worked great!
jezzica85

"Toppers" wrote:

> An alternative:
>
> =INDEX(A:A,MATCH(9.99999999999999E+307,A:A))/A2
>
> The INDEX will find the last value.
>
> HTH
>
> "Toppers" wrote:
>
> > One way is to define your data as a dynamic range e.g. List
> >
> > Insert=>Name=>Define
> >
> > Enter "List" as name (for example)
> >
> > In the Refers To box, enter an Offset formula that defines the range size,
> > based on the number of items in the column, e.g.:
> > =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
> > In this example, the list is on Sheet1, starting in cell A2
> >
> > For your calculation put this in the required cell:
> >
> > =OFFSET(A2,ROWS(List)-1,0)/(A2)
> >
> > This divides last number in range "List" by first
> >
> > HTH
> >
> > "jezzica85" wrote:
> >
> > > Hi all, I have a spreadsheet like this:
> > > 3000
> > > 4000
> > > 5000
> > > 6000
> > >
> > > I want to divide the last number by 3000, and only the last number. If I
> > > add another number on after the last one, say 7000, I want to divide that
> > > one, and only that one. Is there a way to do that?
> > > Thanks!

[L. Howard Kittle]

I saw this in a just posted query.

=LOOKUP(2,1/(range<>""),range)

....added the /3000

=LOOKUP(2,1/(range<>""),range)/3000

HTH
Regards,
Howard

"jezzica85" <jezzica85@discussions.microsoft.com> wrote in message
news:BF09CB89-4E44-4013-BF43-F305351A92B3@microsoft.com...
> Hi all, I have a spreadsheet like this:
> 3000
> 4000
> 5000
> 6000
>
> I want to divide the last number by 3000, and only the last number. If I
> add another number on after the last one, say 7000, I want to divide that
> one, and only that one. Is there a way to do that?
> Thanks!