Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
user thirdbridge said:
Fetch a value based on a date range
On Sheet1, I have a column of bagels. For each type of bagel, there is a series of date / baker's dozen price / single price groups of data - where the date in each triple column group represents the date the new price for the baker's dozen went into effect. The single column is a baker's dozen calculation simply dividing by 12 to get the single price. See below:
A (Bagel) B (Date) C (12) D (1) E (Date) F (12) G (1) H (Date) I (12) J (1)
1 Plain 6/1/11 $15 $1.25 8/19/11 $17 $1.42 11/1/11 $22 $1.83
2 Blueberry 4/15/11 $15 $1.25
3 Everything 6/1/11 $15 $1.25 10/12/11 $17 $1.42
4 Whole Grain 5/25/11 $15 $1.25 7/19/11 $19 $1.53 11/1/11 $22 $1.83
On Sheet2, I have a report with a date at the top. The date falls between the dates listed above.
How can I reference the dates on Sheet1 and return the corresponding single bagel price that fits the date listed on Sheet2?
For example: if the date on Sheet2 is 9/1/11, how do I show that the price of the Plain bagel on that day was $1.42?
Thanks!
---------------------------------
follwing is one solution to the problem ...
user thirdbridge said:
Fetch a value based on a date range
On Sheet1, I have a column of bagels. For each type of bagel, there is a series of date / baker's dozen price / single price groups of data - where the date in each triple column group represents the date the new price for the baker's dozen went into effect. The single column is a baker's dozen calculation simply dividing by 12 to get the single price. See below:
A (Bagel) B (Date) C (12) D (1) E (Date) F (12) G (1) H (Date) I (12) J (1)
1 Plain 6/1/11 $15 $1.25 8/19/11 $17 $1.42 11/1/11 $22 $1.83
2 Blueberry 4/15/11 $15 $1.25
3 Everything 6/1/11 $15 $1.25 10/12/11 $17 $1.42
4 Whole Grain 5/25/11 $15 $1.25 7/19/11 $19 $1.53 11/1/11 $22 $1.83
On Sheet2, I have a report with a date at the top. The date falls between the dates listed above.
How can I reference the dates on Sheet1 and return the corresponding single bagel price that fits the date listed on Sheet2?
For example: if the date on Sheet2 is 9/1/11, how do I show that the price of the Plain bagel on that day was $1.42?
Thanks!
---------------------------------
follwing is one solution to the problem ...
user thirdbridge commented ...
ReplyDeleteUnfortunately, for the project I am trying to build, Sheet2 does not line up the bagels the same (they are sorted differently and sometimes do not show up at all) i.e. I don't know that the bagel I am looking for is on row 1. Is there a way to include a VLOOKUP of sorts or MATCH to determine what row the referring bagel's price sits on first?
I have added another sheet (Sheet4) in this post ...
solution in this sheet provides for entering the BagelType and the associated Date ... and then one can fetch the price for the requested BagleType.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com