• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding the corresponding date for a 52-week high value, based on the most recent data entry

forwork

New Member
Hi,

This is my first time posting here. I learned about this forum from http://reddit.com/r/excel.

I've attached a simplified version of my workbook as an example. There are two sheets: "Dailies" and "Calc". On the "Dailies" sheet I enter the date and some data values every Monday-Friday. I've included 2 "Value" columns as an example.

I want the "Calc" sheet to automatically update its calculations based on the latest entry on the "Dailies" sheet.

One of my calculations is the "52-Week High" which calculates the highest data value in the last year's worth of data, looking back from the most recent data entry. My problem is calculating the corresponding date that the data value peaked on.

I have been using INDEX-MATCH functions for these calculations. However, when I try to calculate the corresponding date of the "52-week high/low" values, the function returns #REF!.

I'm assuming this is because I can't nest INDEX functions, but I don't actually know.

Please let me know if this question is clear and/or you need more information. Any insight would be greatly appreciated.

Thanks!

edit: I've edited the post above to make the issue clearer.
 

Attachments

Last edited:

forwork

New Member
@SirJB7 ,

I took a look through the new users post. I didn't see my problem in the "common problems" thread. Did I miss something I shouldn't have?

In regards to B6:C6 and B11:C11, I expect to see these dates:

B6: 22-Jan-13 ('Dailies' cell A1485)
C6: 23-Jan-13 ('Dailies' cell A1486)

B11: 28-Jun-13 ('Dailies' cell A1598)
C11: 27-Jun-13 ('Dailies' cell A1597)

Thanks!
 

SirJB7

Excel Rōnin
Hi, forwork!

You didn't miss anything about searching for your issue within the common problem, in fact, within the yet posted problems at these forums, if you yet did it before posting. But that isn't the only point in the link that I posted, the idea is that you should go thru all the sublinks before posting, as the main topic title states: New Users Please Start Here.

About your problem, how do you consider the 52 week periods? For example today is in the 4th week of 2014 and 22/01/2013 is in the 4th week of last year, so that's a 53 week period: 52 it'd be from 2701/2013 or from 20/01/2013 until last Saturday.

Regards!
 

forwork

New Member
Hi @SirJB7 ,

Good point - I'll take another read through the new user material.

As for the 52-week period: It's the period of time encompassing 52 weeks prior to the most recent data entry. You'll see on the "Dailies" sheet that the most recent data entry is January 10, 2014. That's the date I'm using in my calculations, not today :p.

I'd like to calculate the 52-week (i.e. yearly) average from the most recent date on the "Dailies" tab (i.e. Jan 10, 2014). Because I only enter the data on weekdays, I am counting each week as 5 days. So: 52 weeks * 5 days/week = 260 days.

You'll see that I used 261 days in the formula (for the 52-week max value) in cell 'Calc'B5; I may tweak this, but the method is the same: looking from the most recent data entry back 260 (or 261) days, and finding the maximum value from that dataset.
 

SirJB7

Excel Rōnin
Hi, forwork!

So let me see if I got it right, with embedded questions:
a) You want to take the last entry date (10/01/2014), check to what week of the year it belongs -which is the 1st week of a year? that of the 1st Monday, that of January 1st?- (assuming on Mondays it'd be the 1st), and then take the data from the 52th week before (week 2 of 2013).
b) You say you'll only take 5 days a week from M-F, 52 weeks a year, but the 52 number is very cumbersome regarding on how yo define the week start. Years have 52 weeks and 1 or 2 days, but the week number can vary from 52 to 54 depending on the start definition. Wouldn't it be simpler and easier to take the year before (i.e., from the 11/01/2013)?

Regards!
 

forwork

New Member
Hi @SirJB7 ,

a) I think I over-complicated the question in my first post. What I want do do is find the highest value in the past year's worth of data. I'm only using the term "52-week-high" because it's a financial term. I don't need to check to what week of the year Jan 10/2014 belongs; I only need to count backwards from that date for one calendar year's worth of values (which is 260 values), find the highest value of those 260 values, and then find the corresponding day that high value occured on.

b) This relates to my answer in (a), but you may be right in that it might be simpler and easier to take the year before (find Jan 11/2013) instead of counting backwards.

I'm going to edit my original post to make the question clearer.

Thanks for the input so far!
 

SirJB7

Excel Rōnin
Hi, forwork!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Finding the corresponding date for a 52-week high value, based on the most recent data entry - Daily values (for forwork at chandoo.org).xlsx

I duplicated your original values of worksheet Calc columns B:C to D:E for checking purposes. It's a completely different approach that thanks to a few tricks with named formulas and named ranges, it simplifies formulas a lot.

The few tricks are related to defining 4 named formulas to hold the values of:
last date entered, LastDateCell
first date to consider, FirstDateCell
1st day of past week, Past1WeekDate
1st day of 2nd past week, Past2WeekDate

You check them at range G1:J1.

Named ranges. There're a lot (not a lot, just 14) dynamic named ranges that allow you to expand (add) data and don't have to change anything. Despite of strongly simplifying the formulas used. All the names are referred to worksheet Dailies.

The main name is:
DataOriginalTable, columns A:C
DateOriginalList, column A

The 1st tricky name is:
Data52WeekTable, rows of DataOriginalTable from FirstDateCell to the end ("it")
Date52WeekList, column A of "it"
Value1_52WeekList, column B of "it"
Value2_52WeekList, column C of "it"

These group of for is replicated as:
Past1WeekTable instead of 52WeekTable, for last week
Pas21WeekTable instead of 52WeekTable, for last 2 weeks

You can visually check each name range going to Formulas tab, Defined Names group, Name Manager icon, selecting a named range, and clicking on the "Refers To" formula at bottom of the window (just for named ranges, not for named formulas).

I also replaced the CF conditions changing them for checking against zero instead of null string, because INDEX function always returns zero value if the cell is empty, and it was easier and cleaner to do this than almost duplicating formulas with the construction =if(index(...)="";"";index(...)).

So give it a try and just advise if any issue.

Regards!
 

forwork

New Member
@SirJB7 ,

Thank you so much. That is (what seems like) an incredible amount of work!

This certainly is simpler, and it gives the added value of allowing me to learn more about excel with a solution already in place.

Thanks again!

forwork
 

forwork

New Member
As I work through the names that you created, I may have a couple questions for you. Would you mind answering them?

If so, my first one is this: what does it mean when you use 2 commas together in some formulas?
 

Somendra Misra

Excel Ninja
@forwork

2 commas together in any formula means you are skipping any argument from the formula.
For eg =OFFSET(ref,row,col,height,width) and if you say =OFFSET(ref,,,5,1) than its means for argument row,col you are passing 0 value so you can skip writing 0 in there and directly put a ,

Regards,
 

forwork

New Member
It seems that =OFFSET is commonly used as a method of referencing a set of data. Why is =OFFSET the go-to method of doing this, instead of INDEX-MATCH or some other method?
 

SirJB7

Excel Rōnin
It seems that =OFFSET is commonly used as a method of referencing a set of data. Why is =OFFSET the go-to method of doing this, instead of INDEX-MATCH or some other method?
Hi, forwork!

A first impulse answer would be because it's the built-in function in Excel to do that. A second more meditated answer is because it's the built-in function in Excel to do that!

There're 2 ways to define a dynamic named range. Let us see them:

OffsetTable: =DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;CONTARA(Hoja1!$1:$1)) -----> in english: =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

IndexTable: =Hoja1!$A$2:INDICE(Hoja1!$2:$1000;CONTARA(Hoja1!$A:$A)-1;CONTARA(Hoja1!$1:$1)) -----> in english: =Sheet1!$A$2:INDEX(Shee1!$2:$1000,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

Both OffsetTable and IndexTable names refer to the same range. Using the INDEX function to define a named range as you asked (only INDEX, INDEX/MATCH is used for searching) might falsely appear to be a better and more efficient and fast method than using OFFSET since OFFSET is a volatile function and INDEX not. Well, that's not true because when you use INDEX combined with a range reference : operator it becomes volatile, so nothing is gained. And two more things: OFFSET syntax is simpler than the range definition with INDEX and doesn't require a fixed range like INDEX, which sounds like a contradiction to define a dynamic thing.
http://forum.chandoo.org/threads/define-dynamic-range-using-index.13561/#post-79982

Hope it helps.

Regards!

PS: Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
 

forwork

New Member
Thanks @SirJB7 , very informative!

Pretty amazing you can do this while also translating into english.

So hypothetically, if I were to increase the number of values I track from 2 to 20 (Value 1 --> Value 20 along the top row), is the easiest way to do these calculations just to create new names for each value, such as Value3_52WeekList, Value4_52WeekList, etc?
 

SirJB7

Excel Rōnin
Hi, forwork!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 

hatif

New Member
Hi, forwork!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Finding the corresponding date for a 52-week high value, based on the most recent data entry - Daily values (for forwork at chandoo.org).xlsx

I duplicated your original values of worksheet Calc columns B:C to D:E for checking purposes. It's a completely different approach that thanks to a few tricks with named formulas and named ranges, it simplifies formulas a lot.

The few tricks are related to defining 4 named formulas to hold the values of:
last date entered, LastDateCell
first date to consider, FirstDateCell
1st day of past week, Past1WeekDate
1st day of 2nd past week, Past2WeekDate

You check them at range G1:J1.

Named ranges. There're a lot (not a lot, just 14) dynamic named ranges that allow you to expand (add) data and don't have to change anything. Despite of strongly simplifying the formulas used. All the names are referred to worksheet Dailies.

The main name is:
DataOriginalTable, columns A:C
DateOriginalList, column A

The 1st tricky name is:
Data52WeekTable, rows of DataOriginalTable from FirstDateCell to the end ("it")
Date52WeekList, column A of "it"
Value1_52WeekList, column B of "it"
Value2_52WeekList, column C of "it"

These group of for is replicated as:
Past1WeekTable instead of 52WeekTable, for last week
Pas21WeekTable instead of 52WeekTable, for last 2 weeks

You can visually check each name range going to Formulas tab, Defined Names group, Name Manager icon, selecting a named range, and clicking on the "Refers To" formula at bottom of the window (just for named ranges, not for named formulas).

I also replaced the CF conditions changing them for checking against zero instead of null string, because INDEX function always returns zero value if the cell is empty, and it was easier and cleaner to do this than almost duplicating formulas with the construction =if(index(...)="";"";index(...)).

So give it a try and just advise if any issue.

Regards!
Can you share the file again please.
 
Top