Lizard_Crimson
New Member
- Joined
- Jun 10, 2021
- Messages
- 4
- Platform
- MacOS
Hello all! New User here, so bear with me
I'm working on a little pet project, attempting to parse out data from a large date range using smaller date ranges. The goal is to automatically get references given a date range that can be used for math in other cells. Most of this can be done with SUMIFS(cells,">="&date1,cells,"<="&date2), MAXIFS(cells,">="&date1,cells,"<="&date2), etc, but not all functions allow for conditional statements
Recently, I found a post on another forum showing how I could use an array function to achieve the same parsing strategy, but with the slope() function, that being the use of conditionals in the slope function to create an array function. However, when I applied this to my own data and cross-referenced it with data using the slope function and manually entering the correct references, some of the entries match the correct data and some do not. Interestingly enough, incorrect data seems to appear in a pattern. It's one that is hard to describe, so you'll have to review the attached spreadsheet. What I'd like to figure out is the reason why some of these entries don't match, as my understanding was both functions reference the same exact data
Information in the attached spreadsheet
Please see 'Models'! for all the mathematics I am trying to perform. I've highlighted columns of interest. Please excuse all the white space, as I've redacted a lot of unnecessary information before uploading.
Thanks!
~Lizard
Spreasheet: TestData.xlsx
I'm working on a little pet project, attempting to parse out data from a large date range using smaller date ranges. The goal is to automatically get references given a date range that can be used for math in other cells. Most of this can be done with SUMIFS(cells,">="&date1,cells,"<="&date2), MAXIFS(cells,">="&date1,cells,"<="&date2), etc, but not all functions allow for conditional statements
Recently, I found a post on another forum showing how I could use an array function to achieve the same parsing strategy, but with the slope() function, that being the use of conditionals in the slope function to create an array function. However, when I applied this to my own data and cross-referenced it with data using the slope function and manually entering the correct references, some of the entries match the correct data and some do not. Interestingly enough, incorrect data seems to appear in a pattern. It's one that is hard to describe, so you'll have to review the attached spreadsheet. What I'd like to figure out is the reason why some of these entries don't match, as my understanding was both functions reference the same exact data
Information in the attached spreadsheet
Please see 'Models'! for all the mathematics I am trying to perform. I've highlighted columns of interest. Please excuse all the white space, as I've redacted a lot of unnecessary information before uploading.
- Column E: {=SLOPE(IF(SLAMSCR1000_Onemin!$A$5:$A$586650>=B6,IF(SLAMSCR1000_Onemin!$A$5:$A$586650<=C6,SLAMSCR1000_Onemin!$D$5:$D$586650)),IF(SLAMSCR1000_Onemin!$A$5:$A$586650>=B6,IF(SLAMSCR1000_Onemin!$A$5:$A$586650<=C6,SLAMSCR1000_Onemin!$Q$5:$Q$586650)))}
- SLAMSCR1000_Onemin!A5:A586650 are the dates in the longer set of data used for indexing. There are no empty cells in this range
- B6 is the lower bound of the date range I'm interested in. I want a reference to start here
- C6 is the upper bound of the date range I'm interested in. I want a reference to end here
- SLAMSCR1000_Onemin!D5:D586650 is the data I'm interested in parsing and doing math on
- SLAMSCR1000_Onemin!Q5:Q586650 is an index column I added to the data to use as the X's when calculating slope
- Column N: =SLOPE(SLAMSCR1000_Onemin!D463386:D463506,SLAMSCR1000_Onemin!Q463386:Q463506)
- I manually went into the long data, found the lower and upper bounds of the date ranges I want to work with, and plugged in the data and index columns. Supposedly, this is the correct slope if the linear regression
- Column V: =E-N
- I wanted to see the severity of the differences, and to my surprise there was a pattern to the differences
- I flagged non-zero differences in Column W
- this is the column displayed in the graph
Thanks!
~Lizard
Spreasheet: TestData.xlsx