| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dividing the last in a series
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dividing the last in a series
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" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to change ColorIndex for series settings | Charts and Charting in Excel | |||
| Chart -- Source Data... -- Series dialog window | Charts and Charting in Excel | |||
| Can I add an average series to a chart with 2 or more series? | Charts and Charting in Excel | |||
| How to change Series order in a Combination Chart? | Charts and Charting in Excel | |||
| series graph -- one series being added to another series | Charts and Charting in Excel | |||
Linear Mode
