Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
I am creating a spreadsheet that will record weight gained/lost and I have
the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Say your starting weight was entered in A1, and each new weight was
continually entered in the next empty cell in Column A. Try this formula in B1: =A1-LOOKUP(99^99,A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Hi!
Try this: Assume starting weight is in cell A1. Periodic weights are entered in column B starting in B1: =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) Biff "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Thanks Ragdyer - I don't suppose you could explain to me how that works could
you? just so that I know why to do it this way again in future. thanks "Ragdyer" wrote: Say your starting weight was entered in A1, and each new weight was continually entered in the next empty cell in Column A. Try this formula in B1: =A1-LOOKUP(99^99,A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Thanks Biff - I don't suppose you could explain why it works?
Both your formula and Ragdyer's formula work, but I dont understand why. Thanks "Biff" wrote: Hi! Try this: Assume starting weight is in cell A1. Periodic weights are entered in column B starting in B1: =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) Biff "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Sure....
=A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) The IF(COUNT(B:B) is just to make sure there is a weight entered in column B. If there wasn't then LOOKUP(10000,B:B) would return an error. LOOKUP(10000,B:B) This is kind of hard to explain but I'll do it in laymans terms........ You want to find the last numeric value entered in a range. In this particular application the numeric value is a persons weight. The way the function works is if the lookup value (10000) is greater than the MAX value in the lookup array (B:B) then it returns the last entry in the range that is less than the lookup value. So, we use an arbitrary number as the lookup value (1000) that is so large there is no possible way that an entry in the range (B:B) will be greater than this arbitrary number. Here's the logic: No human being can possibly weigh 10,000 lbs. Since the weights in the range are guaranteed to be less than 10,000 the formula returns the last numeric value in the range. Biff "Kath" wrote in message ... Thanks Biff - I don't suppose you could explain why it works? Both your formula and Ragdyer's formula work, but I dont understand why. Thanks "Biff" wrote: Hi! Try this: Assume starting weight is in cell A1. Periodic weights are entered in column B starting in B1: =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) Biff "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
The second half of Biff's explanation is exactly what is happening in my
formula, since we are both using the same function (Lookup). However, I just arbitrarily used a very much larger number then he did (99 to the 99th power) since this will usually fit *all* conditions. Also, I didn't include an error trap for missing data as he did, since I felt that an error message (#N/A) would be a fitting notification that *something* was missing. I thought that a "0" should signify "0" weight loss, and a null ( "" - empty cell ) wouldn't really mean anything, since I presumed that the formula was *not* going to be copied down a column. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Kath" wrote in message ... Thanks Ragdyer - I don't suppose you could explain to me how that works could you? just so that I know why to do it this way again in future. thanks "Ragdyer" wrote: Say your starting weight was entered in A1, and each new weight was continually entered in the next empty cell in Column A. Try this formula in B1: =A1-LOOKUP(99^99,A:A) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Hi.
This is so close to what I want to do, but I can't figure out how to modify the formula to suit my spreadsheet. Instead of showing the difference between the starting weight and the most recent weight, I just want a formula in one cell which will return the value from the most recent entry in the column (the last cell that isn't blank). So, say I have dates in column A and weights in column B. On another worksheet I have a cell that should retun the most recent weight entered in column B. What formula will work? Thanks-- "Biff" wrote: Sure.... =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) The IF(COUNT(B:B) is just to make sure there is a weight entered in column B. If there wasn't then LOOKUP(10000,B:B) would return an error. LOOKUP(10000,B:B) This is kind of hard to explain but I'll do it in laymans terms........ You want to find the last numeric value entered in a range. In this particular application the numeric value is a persons weight. The way the function works is if the lookup value (10000) is greater than the MAX value in the lookup array (B:B) then it returns the last entry in the range that is less than the lookup value. So, we use an arbitrary number as the lookup value (1000) that is so large there is no possible way that an entry in the range (B:B) will be greater than this arbitrary number. Here's the logic: No human being can possibly weigh 10,000 lbs. Since the weights in the range are guaranteed to be less than 10,000 the formula returns the last numeric value in the range. Biff "Kath" wrote in message ... Thanks Biff - I don't suppose you could explain why it works? Both your formula and Ragdyer's formula work, but I dont understand why. Thanks "Biff" wrote: Hi! Try this: Assume starting weight is in cell A1. Periodic weights are entered in column B starting in B1: =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) Biff "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
=LOOKUP(99^99,Sheet1!B:B)
Entered in a cell on Sheet2 Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 15:11:05 -0700, cynomolgous wrote: Hi. This is so close to what I want to do, but I can't figure out how to modify the formula to suit my spreadsheet. Instead of showing the difference between the starting weight and the most recent weight, I just want a formula in one cell which will return the value from the most recent entry in the column (the last cell that isn't blank). So, say I have dates in column A and weights in column B. On another worksheet I have a cell that should retun the most recent weight entered in column B. What formula will work? Thanks-- "Biff" wrote: Sure.... =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) The IF(COUNT(B:B) is just to make sure there is a weight entered in column B. If there wasn't then LOOKUP(10000,B:B) would return an error. LOOKUP(10000,B:B) This is kind of hard to explain but I'll do it in laymans terms........ You want to find the last numeric value entered in a range. In this particular application the numeric value is a persons weight. The way the function works is if the lookup value (10000) is greater than the MAX value in the lookup array (B:B) then it returns the last entry in the range that is less than the lookup value. So, we use an arbitrary number as the lookup value (1000) that is so large there is no possible way that an entry in the range (B:B) will be greater than this arbitrary number. Here's the logic: No human being can possibly weigh 10,000 lbs. Since the weights in the range are guaranteed to be less than 10,000 the formula returns the last numeric value in the range. Biff "Kath" wrote in message ... Thanks Biff - I don't suppose you could explain why it works? Both your formula and Ragdyer's formula work, but I dont understand why. Thanks "Biff" wrote: Hi! Try this: Assume starting weight is in cell A1. Periodic weights are entered in column B starting in B1: =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) Biff "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I find the last value of a range....
Thanks!
"Gord Dibben" wrote: =LOOKUP(99^99,Sheet1!B:B) Entered in a cell on Sheet2 Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 15:11:05 -0700, cynomolgous wrote: Hi. This is so close to what I want to do, but I can't figure out how to modify the formula to suit my spreadsheet. Instead of showing the difference between the starting weight and the most recent weight, I just want a formula in one cell which will return the value from the most recent entry in the column (the last cell that isn't blank). So, say I have dates in column A and weights in column B. On another worksheet I have a cell that should retun the most recent weight entered in column B. What formula will work? Thanks-- "Biff" wrote: Sure.... =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) The IF(COUNT(B:B) is just to make sure there is a weight entered in column B. If there wasn't then LOOKUP(10000,B:B) would return an error. LOOKUP(10000,B:B) This is kind of hard to explain but I'll do it in laymans terms........ You want to find the last numeric value entered in a range. In this particular application the numeric value is a persons weight. The way the function works is if the lookup value (10000) is greater than the MAX value in the lookup array (B:B) then it returns the last entry in the range that is less than the lookup value. So, we use an arbitrary number as the lookup value (1000) that is so large there is no possible way that an entry in the range (B:B) will be greater than this arbitrary number. Here's the logic: No human being can possibly weigh 10,000 lbs. Since the weights in the range are guaranteed to be less than 10,000 the formula returns the last numeric value in the range. Biff "Kath" wrote in message ... Thanks Biff - I don't suppose you could explain why it works? Both your formula and Ragdyer's formula work, but I dont understand why. Thanks "Biff" wrote: Hi! Try this: Assume starting weight is in cell A1. Periodic weights are entered in column B starting in B1: =A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0) Biff "Kath" wrote in message ... I am creating a spreadsheet that will record weight gained/lost and I have the basic spreadsheet done. However, I need to have a cell that returns a value equal to the difference between the starting weight and the most recent weight - which is not necessarily the lowest weight, so I can't use MIN() Values are being added every few days and as such Excel needs to update as needed each time. All weight data is entered within the same column. Has anyone got any advice please. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel auto filtering to find a range of dates in a list | Excel Discussion (Misc queries) | |||
Search range of cells, find a value, output adjoining cell. How? | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
I need a formula to find rows within a date range in one column? | Excel Worksheet Functions |