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 crossreferenced 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 crossreferenced 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: =EN
 I wanted to see the severity of the differences, and to my surprise there was a pattern to the differences
 I flagged nonzero differences in Column W
 this is the column displayed in the graph
Thanks!
~Lizard
Spreasheet: TestData.xlsx