Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup and Date
I have one table of balance information and I track the balance daily.
Then I have a summary table that pulls growth numbers from that table for 1 week, 1 month, and 3 months. The formula in my summary table for 1 month is - =(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000,2,FALSE))) Here's my problem - I only enter these numbers during the week. So on December 13th my table looks back 1 month and errors out because Nomveber 13th was a Saturday and I didn't enter a number. In this case I need the formula to go to the last balance entered which would have been November 12th's Friday balance. Is there a way to modify my formula so that if the date doesn't exist in the table then it will go to the most recent previous entry -- If one month ago (or three months ago) is a Saturday or Sunday it calculates off of the Friday balance? I guess I could always enter weekend days in my table and just copy and paste over the data from Friday, but I hope there's an easier way so that I don't have to add meaningless records to my table. Ted |
#2
|
|||
|
|||
Hi
instead of VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance !$A$4:$E$10000,2,FALSE) try using the followung array formula (entered with CTRL+SHIFT+ENTER): INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Perfo rmance!$A$4:$A$10000< =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$ A$10000)),Performance!$A$4:$A$10000,0)) -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... I have one table of balance information and I track the balance daily. Then I have a summary table that pulls growth numbers from that table for 1 week, 1 month, and 3 months. The formula in my summary table for 1 month is - =(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000, 2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1) ),Performance!$A$4:$E$10000,2,FALSE))) Here's my problem - I only enter these numbers during the week. So on December 13th my table looks back 1 month and errors out because Nomveber 13th was a Saturday and I didn't enter a number. In this case I need the formula to go to the last balance entered which would have been November 12th's Friday balance. Is there a way to modify my formula so that if the date doesn't exist in the table then it will go to the most recent previous entry -- If one month ago (or three months ago) is a Saturday or Sunday it calculates off of the Friday balance? I guess I could always enter weekend days in my table and just copy and paste over the data from Friday, but I hope there's an easier way so that I don't have to add meaningless records to my table. Ted |
#3
|
|||
|
|||
That actually doesn't do what I need it to do.
So right now here is how I have my data set up. Column A Column B 11/29/2004 986.5 11/30/2004 1000 12/1/2004 1247 12/2/2004 1124 12/3/2004 1160 12/6/2004 1152.5 12/7/2004 1194 So my 1 week formula on 12-7 calculates the growth of 1 week and calculates -- (1194-1000)/1000 -- and returns 19.4%. You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a date. However next month on 1/5/2005 when I calculate my 1 month growth I won't have a 12/5/2004 date so I need it to pull the most recent number which is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005 number] - 1160)/1160. "Frank Kabel" wrote: Hi instead of VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance !$A$4:$E$10000,2,FALSE) try using the followung array formula (entered with CTRL+SHIFT+ENTER): INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Perfo rmance!$A$4:$A$10000< =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$ A$10000)),Performance!$A$4:$A$10000,0)) -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... I have one table of balance information and I track the balance daily. Then I have a summary table that pulls growth numbers from that table for 1 week, 1 month, and 3 months. The formula in my summary table for 1 month is - =(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000, 2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1) ),Performance!$A$4:$E$10000,2,FALSE))) Here's my problem - I only enter these numbers during the week. So on December 13th my table looks back 1 month and errors out because Nomveber 13th was a Saturday and I didn't enter a number. In this case I need the formula to go to the last balance entered which would have been November 12th's Friday balance. Is there a way to modify my formula so that if the date doesn't exist in the table then it will go to the most recent previous entry -- If one month ago (or three months ago) is a Saturday or Sunday it calculates off of the Friday balance? I guess I could always enter weekend days in my table and just copy and paste over the data from Friday, but I hope there's an easier way so that I don't have to add meaningless records to my table. Ted |
#4
|
|||
|
|||
Hi
what is the exact formula you have tried and what result did you get? -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... That actually doesn't do what I need it to do. So right now here is how I have my data set up. Column A Column B 11/29/2004 986.5 11/30/2004 1000 12/1/2004 1247 12/2/2004 1124 12/3/2004 1160 12/6/2004 1152.5 12/7/2004 1194 So my 1 week formula on 12-7 calculates the growth of 1 week and calculates -- (1194-1000)/1000 -- and returns 19.4%. You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a date. However next month on 1/5/2005 when I calculate my 1 month growth I won't have a 12/5/2004 date so I need it to pull the most recent number which is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005 number] - 1160)/1160. "Frank Kabel" wrote: Hi instead of VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance !$A$4:$E$10000,2,FALSE) try using the followung array formula (entered with CTRL+SHIFT+ENTER): INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Perfo rmance!$A$4:$A$10000< =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$ A$10000)),Performance!$A$4:$A$10000,0)) -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... I have one table of balance information and I track the balance daily. Then I have a summary table that pulls growth numbers from that table for 1 week, 1 month, and 3 months. The formula in my summary table for 1 month is - =(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000, 2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1) ),Performance!$A$4:$E$10000,2,FALSE))) Here's my problem - I only enter these numbers during the week. So on December 13th my table looks back 1 month and errors out because Nomveber 13th was a Saturday and I didn't enter a number. In this case I need the formula to go to the last balance entered which would have been November 12th's Friday balance. Is there a way to modify my formula so that if the date doesn't exist in the table then it will go to the most recent previous entry -- If one month ago (or three months ago) is a Saturday or Sunday it calculates off of the Friday balance? I guess I could always enter weekend days in my table and just copy and paste over the data from Friday, but I hope there's an easier way so that I don't have to add meaningless records to my table. Ted |
#5
|
|||
|
|||
My one month formula is the one i listed earlier --
=(VLOOKUP(TODAY(),Performance!$A$4:$E$10000,2,FALS E)-(VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),Performance!$A$4:$E$10000,2,FALSE) ))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),Performance!$A$4:$E$10000,2,FALSE) )) this takes today's balance, subtracts the balance from 1 month ago and then calculates the change in percentage terms. The problem is that on 12/14/2004 when it looks for the 11/14/2004 date it won't find it because 11/14/2004 was a Sunday. So I'll want it to take the 12/14 balance and subtract the 11/12 balance and then calculate the change. The problem isn't the vlookup it's the problem with weekends. I only have weekday data (Monday through Friday), so when 12/14 is a Tuesday and 11/14 is a Sunday I need the formula to grab the closest possible date, which will be the 11/12 number. Does that make sense? "Frank Kabel" wrote: Hi what is the exact formula you have tried and what result did you get? -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... That actually doesn't do what I need it to do. So right now here is how I have my data set up. Column A Column B 11/29/2004 986.5 11/30/2004 1000 12/1/2004 1247 12/2/2004 1124 12/3/2004 1160 12/6/2004 1152.5 12/7/2004 1194 So my 1 week formula on 12-7 calculates the growth of 1 week and calculates -- (1194-1000)/1000 -- and returns 19.4%. You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a date. However next month on 1/5/2005 when I calculate my 1 month growth I won't have a 12/5/2004 date so I need it to pull the most recent number which is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005 number] - 1160)/1160. "Frank Kabel" wrote: Hi instead of VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance !$A$4:$E$10000,2,FALSE) try using the followung array formula (entered with CTRL+SHIFT+ENTER): INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Perfo rmance!$A$4:$A$10000< =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$ A$10000)),Performance!$A$4:$A$10000,0)) -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... I have one table of balance information and I track the balance daily. Then I have a summary table that pulls growth numbers from that table for 1 week, 1 month, and 3 months. The formula in my summary table for 1 month is - =(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000, 2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1) ),Performance!$A$4:$E$10000,2,FALSE))) Here's my problem - I only enter these numbers during the week. So on December 13th my table looks back 1 month and errors out because Nomveber 13th was a Saturday and I didn't enter a number. In this case I need the formula to go to the last balance entered which would have been November 12th's Friday balance. Is there a way to modify my formula so that if the date doesn't exist in the table then it will go to the most recent previous entry -- If one month ago (or three months ago) is a Saturday or Sunday it calculates off of the Friday balance? I guess I could always enter weekend days in my table and just copy and paste over the data from Friday, but I hope there's an easier way so that I don't have to add meaningless records to my table. Ted |
#6
|
|||
|
|||
Looked at excel's help and I think i fixed it. If I change my logical values
to TRUE from FALSE Excel will grab the next largest date that is less than the date it's looking for, when it cannot find an exact match. This appears to solve my problem. "Frank Kabel" wrote: Hi what is the exact formula you have tried and what result did you get? -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... That actually doesn't do what I need it to do. So right now here is how I have my data set up. Column A Column B 11/29/2004 986.5 11/30/2004 1000 12/1/2004 1247 12/2/2004 1124 12/3/2004 1160 12/6/2004 1152.5 12/7/2004 1194 So my 1 week formula on 12-7 calculates the growth of 1 week and calculates -- (1194-1000)/1000 -- and returns 19.4%. You can see that 12/4 and 12/5 were Saturday and Sunday so I didn't enter a date. However next month on 1/5/2005 when I calculate my 1 month growth I won't have a 12/5/2004 date so I need it to pull the most recent number which is the 12/3/2004 number. At which time it will calculate -- ([1/5/2005 number] - 1160)/1160. "Frank Kabel" wrote: Hi instead of VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance !$A$4:$E$10000,2,FALSE) try using the followung array formula (entered with CTRL+SHIFT+ENTER): INDEX(Performance!$C$4:$C$10000,MATCH(MAX(IF(Perfo rmance!$A$4:$A$10000< =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$ A$10000)),Performance!$A$4:$A$10000,0)) -- Regards Frank Kabel Frankfurt, Germany "Ted Metro" schrieb im Newsbeitrag ... I have one table of balance information and I track the balance daily. Then I have a summary table that pulls growth numbers from that table for 1 week, 1 month, and 3 months. The formula in my summary table for 1 month is - =(VLOOKUP(TODAY()-1,Performance!$A$4:$E$10000,2,FALSE)-(VLOOKUP(DATE(YE AR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1)),Performance!$A$4:$E$10000, 2,FALSE)))/((VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()-1) ),Performance!$A$4:$E$10000,2,FALSE))) Here's my problem - I only enter these numbers during the week. So on December 13th my table looks back 1 month and errors out because Nomveber 13th was a Saturday and I didn't enter a number. In this case I need the formula to go to the last balance entered which would have been November 12th's Friday balance. Is there a way to modify my formula so that if the date doesn't exist in the table then it will go to the most recent previous entry -- If one month ago (or three months ago) is a Saturday or Sunday it calculates off of the Friday balance? I guess I could always enter weekend days in my table and just copy and paste over the data from Friday, but I hope there's an easier way so that I don't have to add meaningless records to my table. Ted |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost on Vlookup, match, etc.... | New Users to Excel | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) | |||
Vlookup Question | Excel Discussion (Misc queries) | |||
Nested vlookup? | Excel Worksheet Functions | |||
Date VLookup | Excel Worksheet Functions |