Hey all, long time lurker, first time poster.
I'm currently having trouble finding a way to write a formula that v-vlookups a specified date and returns a value but also return the closest previous date if the specified date doesn't exist in the data set. I have successfully written a formula that can do this, but there are situations where I would need to nest 10+ IF statements to essentially cover all possibilities. Let me explain:
I am trying to build a very basic order growth predictor for the current sales year based on the previous 5 years of sales data. I want to be able to specify a date range in the top left corner of the file, and then populate the table with data by team based on the date range and year. I have data pulled by sales team and cumulative orders by day in another tab. Essentially the question I am trying to answer is "Hey, how much did orders grow between these two dates for this year range?". The issue I'm having is that you would think our data records would have 365 individual records for each year per sales team(that's how many days in a year...duh!), but if there was no order changes for a certain day, the day doesn't exist.
So say if I wanted to look up what the orders were on March 27th, and June 1st for Team A in 2013-2014.
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Start Year[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]End Year[/TD]
[TD]2013[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]03[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]06[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]
Here is the table that gets populated
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]Team[/TD]
[TD]27-Mar[/TD]
[TD]1-Jun[/TD]
[TD]% Growth[/TD]
[TD]27-Mar[/TD]
[TD]1-Jun[/TD]
[TD]% Growth[/TD]
[/TR]
[TR]
[TD]Pacific[/TD]
[TD]A[/TD]
[TD]328,084[/TD]
[TD]328,294[/TD]
[TD].1%[/TD]
[TD]254,594[/TD]
[TD]260,259[/TD]
[TD]2.2%[/TD]
[/TR]
[TR]
[TD]Pacific[/TD]
[TD]B[/TD]
[TD]165,114[/TD]
[TD]163,813[/TD]
[TD]-.8%[/TD]
[TD]188,442[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]Pacific[/TD]
[TD]C[/TD]
[TD]325,289[/TD]
[TD]320,751[/TD]
[TD]-1.4%[/TD]
[TD]270,255[/TD]
[TD]260,208[/TD]
[TD]-3.7%[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, the record for June 1st 2014 in Team B doesn't exist so it gives back an N/A. Below you can see the table set where June 1st is missing along with May 30th. What I want to do is have a formula that can recognize that the date specified doesn't exist and then look back in history for the next date
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Day[/TD]
[TD]Order Change[/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]5/30/2013[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]6/02/2013[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]6/03/2013[/TD]
[TD]-191[/TD]
[/TR]
</tbody>[/TABLE]
Here is my current formula:
=IFERROR(IF($G$14<$B$2,"",VLOOKUP(CONCATENATE($C17,$H$15),Pivot!$A:$H,8,FALSE)),IF($G$14<$B$2,"",VLOOKUP(CONCATENATE($C17,$H$15-1),Pivot!$A:$H,8,FALSE)))
As you can see my current formula has an IFERROR that is essentially stating "If you get an error because the date doesn't exist, go back one day and grab that one". Since I have a two day gap it doesn't work. Now like I said in the beginning of the post, I could nest "IF" formulas all day long until I basically cover the largest data gap I have and call it a day. But since I like to learn new things and do things right, I wanted to see if there is anyway to CORRECTLY write this to take care of it. Any thoughts?
I'm currently having trouble finding a way to write a formula that v-vlookups a specified date and returns a value but also return the closest previous date if the specified date doesn't exist in the data set. I have successfully written a formula that can do this, but there are situations where I would need to nest 10+ IF statements to essentially cover all possibilities. Let me explain:
I am trying to build a very basic order growth predictor for the current sales year based on the previous 5 years of sales data. I want to be able to specify a date range in the top left corner of the file, and then populate the table with data by team based on the date range and year. I have data pulled by sales team and cumulative orders by day in another tab. Essentially the question I am trying to answer is "Hey, how much did orders grow between these two dates for this year range?". The issue I'm having is that you would think our data records would have 365 individual records for each year per sales team(that's how many days in a year...duh!), but if there was no order changes for a certain day, the day doesn't exist.
So say if I wanted to look up what the orders were on March 27th, and June 1st for Team A in 2013-2014.
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Start Year[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]End Year[/TD]
[TD]2013[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]Month[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]03[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]06[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]
Here is the table that gets populated
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]Team[/TD]
[TD]27-Mar[/TD]
[TD]1-Jun[/TD]
[TD]% Growth[/TD]
[TD]27-Mar[/TD]
[TD]1-Jun[/TD]
[TD]% Growth[/TD]
[/TR]
[TR]
[TD]Pacific[/TD]
[TD]A[/TD]
[TD]328,084[/TD]
[TD]328,294[/TD]
[TD].1%[/TD]
[TD]254,594[/TD]
[TD]260,259[/TD]
[TD]2.2%[/TD]
[/TR]
[TR]
[TD]Pacific[/TD]
[TD]B[/TD]
[TD]165,114[/TD]
[TD]163,813[/TD]
[TD]-.8%[/TD]
[TD]188,442[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]Pacific[/TD]
[TD]C[/TD]
[TD]325,289[/TD]
[TD]320,751[/TD]
[TD]-1.4%[/TD]
[TD]270,255[/TD]
[TD]260,208[/TD]
[TD]-3.7%[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, the record for June 1st 2014 in Team B doesn't exist so it gives back an N/A. Below you can see the table set where June 1st is missing along with May 30th. What I want to do is have a formula that can recognize that the date specified doesn't exist and then look back in history for the next date
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Day[/TD]
[TD]Order Change[/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]5/30/2013[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]6/02/2013[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Team B[/TD]
[TD]6/03/2013[/TD]
[TD]-191[/TD]
[/TR]
</tbody>[/TABLE]
Here is my current formula:
=IFERROR(IF($G$14<$B$2,"",VLOOKUP(CONCATENATE($C17,$H$15),Pivot!$A:$H,8,FALSE)),IF($G$14<$B$2,"",VLOOKUP(CONCATENATE($C17,$H$15-1),Pivot!$A:$H,8,FALSE)))
As you can see my current formula has an IFERROR that is essentially stating "If you get an error because the date doesn't exist, go back one day and grab that one". Since I have a two day gap it doesn't work. Now like I said in the beginning of the post, I could nest "IF" formulas all day long until I basically cover the largest data gap I have and call it a day. But since I like to learn new things and do things right, I wanted to see if there is anyway to CORRECTLY write this to take care of it. Any thoughts?
