Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Most Recent Values in Col1 -- Summing Matching Values
I'm thinking this isn't possible, but thought I'd float it out to see if
anyone had any ideas. I have the following columns of data (an excerpt; there's about 300 records in my table): TimeID Num1 Num2 1 4 4 2 5 4 3 6 2 4 5 3 5 3 3 6 4 4 7 4 3 8 6 2 ------------------------------ 9 5 4 Is there a single formula that can: 1) find the most recent values for each, unique Num1 (e.g. for Num1=6, it'd be TimeID=8), 2) See if any of the correlating values to the Num1 values in Num2 from rows 1-8 (not 1-9) match the most recent entry in Num2 minus 1. 3) Sum up only those values in Num2 (rows 1-8) that are from the most recent values in Num1 whose correlating values in Num2 equal the most recent entry in Num2 minus 1. So, in the excerpt above, the cell of this magic formula would return a value of 9, if you can follow this. I'm not holding my breath, but there you have it! Thanks a bunch in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Most Recent Values in Col1 -- Summing Matching Values
"Rothman" wrote:
I'm thinking this isn't possible, but thought I'd float it out to see if anyone had any ideas. I have the following columns of data (an excerpt; there's about 300 records in my table): TimeID Num1 Num2 1 4 4 2 5 4 3 6 2 4 5 3 5 3 3 6 4 4 7 4 3 8 6 2 ------------------------------ 9 5 4 Is there a single formula that can: 1) find the most recent values for each, unique Num1 (e.g. for Num1=6, it'd be TimeID=8), 2) See if any of the correlating values to the Num1 values in Num2 from rows 1-8 (not 1-9) match the most recent entry in Num2 minus 1. 3) Sum up only those values in Num2 (rows 1-8) that are from the most recent values in Num1 whose correlating values in Num2 equal the most recent entry in Num2 minus 1. So, in the excerpt above, the cell of this magic formula would return a value of 9, if you can follow this. I'm not holding my breath, but there you have it! Thanks a bunch in advance! I take it this is an impossibility? Or is there confusion over what I'm asking? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Most Recent Values in Col1 -- Summing Matching Values
For your #1, check out the "Arbitrary Lookups" section of this page:
http://www.cpearson.com/excel/lookups.htm HTH, JP On Dec 13, 2:01 pm, Rothman wrote: I'm thinking this isn't possible, but thought I'd float it out to see if anyone had any ideas. I have the following columns of data (an excerpt; there's about 300 records in my table): TimeID Num1 Num2 1 4 4 2 5 4 3 6 2 4 5 3 5 3 3 6 4 4 7 4 3 8 6 2 ------------------------------ 9 5 4 Is there a single formula that can: 1) find the most recent values for each, unique Num1 (e.g. for Num1=6, it'd be TimeID=8), |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Most Recent Values in Col1 -- Summing Matching Values
This solution is sort of like a first step, which is one step closer than I
was. There are currently 24 distinct values in my Num1 column. The formula in that link only finds the most recent value for one of those values. Is there someway to alter the formula to where Excel can handle multiple recent values in one fell swoop (i.e. match up those recent values with the most recent value in Num2-1? Maybe there's someway to count them (the most recent, unique values in Num1 that have correlating values in Num2 which match the most recent Num2-1) up and then multiply by the most recent value in Num2? Thanks again for your and anyone else's help. "JP" wrote: For your #1, check out the "Arbitrary Lookups" section of this page: http://www.cpearson.com/excel/lookups.htm HTH, JP On Dec 13, 2:01 pm, Rothman wrote: I'm thinking this isn't possible, but thought I'd float it out to see if anyone had any ideas. I have the following columns of data (an excerpt; there's about 300 records in my table): TimeID Num1 Num2 1 4 4 2 5 4 3 6 2 4 5 3 5 3 3 6 4 4 7 4 3 8 6 2 ------------------------------ 9 5 4 Is there a single formula that can: 1) find the most recent values for each, unique Num1 (e.g. for Num1=6, it'd be TimeID=8), |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Most Recent Values in Col1 -- Summing Matching Values
By "most recent" do you mean the last entry in each column?
If the TimeID column will not have any blanks, this formula will tell you the last entry in the column: =INDEX($A:$A,COUNTA($A:$A),1) Can you provide an example of points 2 and 3? On Dec 20, 2:22 pm, Rothman wrote: This solution is sort of like a first step, which is one step closer than I was. There are currently 24 distinct values in my Num1 column. The formula in that link only finds the most recent value for one of those values. Is there someway to alter the formula to where Excel can handle multiple recent values in one fell swoop (i.e. match up those recent values with the most recent value in Num2-1? Maybe there's someway to count them (the most recent, unique values in Num1 that have correlating values in Num2 which match the most recent Num2-1) up and then multiply by the most recent value in Num2? Thanks again for your and anyone else's help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding Most Recent Values in Col1 -- Summing Matching Values
Sorry about the confusion. Here's that original excerpt that I put in the
OP, but let's just look at it as if it was the total set: TimeID Num1 Num2 1 4 4 2 5 4 3 6 2 4 5 3 5 3 3 6 4 4 7 4 3 8 6 2 ------------------------------ 9 5 4 In Num1, there are 4 distinct values: 3,4,5,6. The most recent of each of these values have matching Num2 values of 3,3,3,2 respectively (don't take into account the most recent entry). The most recent value of Num2-1 = 3. Therefore, summing the 3s up that were found before would equal 9, which is the number I really want. The other option I was considering is somehow counting up the most recent values in Num1 that have Num2-1 and then multiplying them by Num2-1... Like I said originally, I'm not holding my breath here. :) "JP" wrote: By "most recent" do you mean the last entry in each column? If the TimeID column will not have any blanks, this formula will tell you the last entry in the column: =INDEX($A:$A,COUNTA($A:$A),1) Can you provide an example of points 2 and 3? On Dec 20, 2:22 pm, Rothman wrote: This solution is sort of like a first step, which is one step closer than I was. There are currently 24 distinct values in my Num1 column. The formula in that link only finds the most recent value for one of those values. Is there someway to alter the formula to where Excel can handle multiple recent values in one fell swoop (i.e. match up those recent values with the most recent value in Num2-1? Maybe there's someway to count them (the most recent, unique values in Num1 that have correlating values in Num2 which match the most recent Num2-1) up and then multiply by the most recent value in Num2? Thanks again for your and anyone else's help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Values in a Row | Excel Worksheet Functions | |||
summing values in one row based on values in another row | Excel Worksheet Functions | |||
finding values and displaying adjacent values | Excel Worksheet Functions | |||
LOOKING FOR A NAME, THEN SUMMING ALL VALUES ASSOCIATED WITH NAME | Excel Discussion (Misc queries) | |||
searching for values and summing the corresponding values | Excel Worksheet Functions |