Using an array function gives different results than cross-referenced function

L

Lizard_Crimson

New Member
Joined
Jun 10, 2021
Messages
4
Platform
  1. 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.
  • 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
If anyone might know of some reading material I could go through to understand why the slope functions don't match, I would greatly appreciate it. I'm working off of MacOS, excel version 16.49. The attached spreadsheet was uploaded in that format

Thanks!
~Lizard

Spreasheet: TestData.xlsx
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
jasonb75 said:
It looks like you're getting floating point precision errors. This can sometimes be fixed by rounding, although there may be better alternatives.

docs.microsoft.com

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.
docs.microsoft.com
Click to expand...
That doesn't make sense to me. If the function in column E is doing the same thing as the function in column N, with the only difference being in how reference cells are indexed, wouldn't they both have the same precision error? If the larger precision error is in column E due to the function being more complex, would that mean column N is more accurate?
 
Upvote 0
jasonb75 said:
It looks like you're getting floating point precision errors. This can sometimes be fixed by rounding, although there may be better alternatives.

docs.microsoft.com

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.
docs.microsoft.com
Click to expand...
I think I understand what you are saying now. A precision error is probably occurring in the function's use of the date identifier, since that has multiple trailing decimals.

I guess I've hit Excel's limit in this regard
 
Upvote 0
Given that you are using 1 minute intervals in your raw data one way to work around the problem would be to subtract 1 second from B6 and a dd 1 second to C6.

Obviously this workaround becomes less suitable when greater accuracy is required.

Also, with data that is sorted, I would personally look at ways to find the start and end of the desired range of raw data rather than processing ~500k rows of data that fall outside of the criteria scope.
In principle something like this, with 1 second added to both start and end point to allow for floating point errors would create the same range as your column N test formula dynamically, meaning that only the relevant ~120 rows for the 2 hour window are processed in the array instead of the entire range.
Excel Formula: 
=SLOPE(INDEX(x_range,MATCH(start point,date_range)):INDEX(x_range,MATCH(end point,date_range)),INDEX(y_range,MATCH(start point,date_range)):INDEX(y_range,MATCH(end point,date_range)))
 
Upvote 0
Solution
jasonb75 said:
Given that you are using 1 minute intervals in your raw data one way to work around the problem would be to subtract 1 second from B6 and a dd 1 second to C6.

Obviously this workaround becomes less suitable when greater accuracy is required.

Also, with data that is sorted, I would personally look at ways to find the start and end of the desired range of raw data rather than processing ~500k rows of data that fall outside of the criteria scope.
In principle something like this, with 1 second added to both start and end point to allow for floating point errors would create the same range as your column N test formula dynamically, meaning that only the relevant ~120 rows for the 2 hour window are processed in the array instead of the entire range.
Excel Formula: 
=SLOPE(INDEX(x_range,MATCH(start point,date_range)):INDEX(x_range,MATCH(end point,date_range)),INDEX(y_range,MATCH(start point,date_range)):INDEX(y_range,MATCH(end point,date_range)))
Click to expand...

I FOUND THE PROBLEM!!

Notice in the spreadsheet below how "precision errors" occur when there is a discrepency in the index value (I added some index columns to see where the data starts and ends). If I manually change the index to the correct value, the errors go away. So this was an indexing error. I'll play around with rounding out dates such that the index is always correct

Thanks!

TestData2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1DateTimeStart DateTimeEnd DateTimeslope of rangemax - minreported valueindex_lowerindex_upperDateTimeStart DateTimeEnd DateTimeslope of rangem(x_2) - m(x_1)reported valueWeighted Resultreported valTest Data
23/1/21 1:003/1/21 0:003/1/21 2:00-0.00979082.2424633824635023/1/21 1:003/1/21 0:003/1/21 2:00-0.0097908-1.174897712.2420
33/1/21 3:003/1/21 2:003/1/21 4:00-0.00030051.914635024636223/1/21 3:003/1/21 2:003/1/21 4:00-0.0003005-0.035759701.910
43/1/21 5:003/1/21 4:003/1/21 6:00-0.00873872.6424636224637423/1/21 5:003/1/21 4:003/1/21 6:00-0.0087387-1.039907812.6420
53/1/21 7:003/1/21 6:003/1/21 8:000.009875762.7404637424638623/1/21 7:003/1/21 6:003/1/21 8:000.009875761.1850914502.7400
63/1/21 9:003/1/21 8:003/1/21 10:000.006809858.3604638624639823/1/21 9:003/1/21 8:003/1/21 10:000.006809850.810372108.3600
73/1/21 11:003/1/21 10:003/1/21 12:000.023920279.0904639824641023/1/21 11:003/1/21 10:003/1/21 12:000.023920272.8465119209.0900
83/1/21 13:003/1/21 12:003/1/21 14:00-0.00925416.164641024642213/1/21 13:003/1/21 12:003/1/21 14:00-0.0090747-1.079883516.16-0.0001795XXXXX
93/1/21 15:003/1/21 14:003/1/21 16:00-0.00565726.664642214643423/1/21 15:003/1/21 14:003/1/21 16:00-0.0057899-0.694783906.660.00013262XXXXX
103/1/21 17:003/1/21 16:003/1/21 18:00-0.056346116.51164643424644623/1/21 17:003/1/21 16:003/1/21 18:00-0.0563461-6.7051849616.51160
113/1/21 19:003/1/21 18:003/1/21 20:000.003234886.5204644624645813/1/21 19:003/1/21 18:003/1/21 20:000.003339860.3974432306.520-0.000105XXXXX
123/1/21 21:003/1/21 20:003/1/21 22:00-0.02289843.734645814647023/1/21 21:003/1/21 20:003/1/21 22:00-0.0225308-2.703690623.73-0.0003676XXXXX
133/1/21 23:003/1/21 22:003/2/21 0:00-0.00173032.7324647024648223/1/21 23:003/1/21 22:003/2/21 0:00-0.0017303-0.205908202.7320
143/2/21 1:003/2/21 0:003/2/21 2:00-0.008552.5924648224649413/2/21 1:003/2/21 0:003/2/21 2:00-0.008387-0.998053902.592-0.000163XXXXX
153/2/21 3:003/2/21 2:003/2/21 4:00-0.00504571.5214649414650623/2/21 3:003/2/21 2:003/2/21 4:00-0.0052423-0.629077401.5210.00019659XXXXX
163/2/21 5:003/2/21 4:003/2/21 6:000.001994451.5604650624651823/2/21 5:003/2/21 4:003/2/21 6:000.001994450.2373389801.5600
173/2/21 7:003/2/21 6:003/2/21 8:00-0.01741183.4634651824653013/2/21 7:003/2/21 6:003/2/21 8:00-0.0174648-2.078308223.4635.294E-05XXXXX
183/2/21 9:003/2/21 8:003/2/21 10:000.035251424.8304653014654223/2/21 9:003/2/21 8:003/2/21 10:000.035296574.2355886704.830-4.515E-05XXXXX
193/2/21 11:003/2/21 10:003/2/21 12:000.004967626.2504654224655423/2/21 11:003/2/21 10:003/2/21 12:000.004967620.5911467306.2500
203/2/21 13:003/2/21 12:003/2/21 14:00-0.01978347.6874655424656613/2/21 13:003/2/21 12:003/2/21 14:00-0.0208935-2.486327727.6870.00111009XXXXX
213/2/21 15:003/2/21 14:003/2/21 16:00-0.00130024.9644656614657823/2/21 15:003/2/21 14:003/2/21 16:00-0.0012098-0.145175504.964-9.038E-05XXXXX
223/2/21 17:003/2/21 16:003/2/21 18:00-0.025397470.511704657824659023/2/21 17:003/2/21 16:003/2/21 18:00-0.0253974-3.0222896370.511700
233/2/21 19:003/2/21 18:003/2/21 20:00-0.00415072.9924659024660213/2/21 19:003/2/21 18:003/2/21 20:00-0.0040502-0.481973402.992-0.0001005XXXXX
243/2/21 21:003/2/21 20:003/2/21 22:00-0.01371713.1234660214661423/2/21 21:003/2/21 20:003/2/21 22:00-0.0137988-1.655856913.1238.1683E-05XXXXX
253/2/21 23:003/2/21 22:003/3/21 0:00-0.0103432.4124661424662623/2/21 23:003/2/21 22:003/3/21 0:00-0.010343-1.23081412.4120
263/3/21 1:003/3/21 0:003/3/21 2:00-0.0129062.1724662624663813/3/21 1:003/3/21 0:003/3/21 2:00-0.0128525-1.529450712.172-5.351E-05XXXXX
273/3/21 3:003/3/21 2:003/3/21 4:00-0.00356241.7814663814665023/3/21 3:003/3/21 2:003/3/21 4:00-0.0036679-0.440151701.7810.00010549XXXXX
283/3/21 5:003/3/21 4:003/3/21 6:000.009413432.9904665024666223/3/21 5:003/3/21 4:003/3/21 6:000.009413431.1201977402.9900
293/3/21 7:003/3/21 6:003/3/21 8:000.002165572.3604666224667413/3/21 7:003/3/21 6:003/3/21 8:000.002138460.2544771702.3602.7103E-05XXXXX
303/3/21 9:003/3/21 8:003/3/21 10:00-0.00955242.4524667414668623/3/21 9:003/3/21 8:003/3/21 10:00-0.0095238-1.142861412.452-2.857E-05XXXXX
313/3/21 11:003/3/21 10:003/3/21 12:00-0.0013727.4174668624669823/3/21 11:003/3/21 10:003/3/21 12:00-0.001372-0.163264307.4170
323/3/21 13:003/3/21 12:003/3/21 14:000.0138545741.2604669824671013/3/21 13:003/3/21 12:003/3/21 14:000.013393241.59379549041.2600.00046133XXXXX
333/3/21 15:003/3/21 14:003/3/21 16:00-0.532891887.979874671014672223/3/21 15:003/3/21 14:003/3/21 16:00-0.5359332-64.3119836487.979870.00304135XXXXX
343/3/21 17:003/3/21 16:003/3/21 18:00-0.131137921.33214672224673423/3/21 17:003/3/21 16:003/3/21 18:00-0.1311379-15.6054131521.33210
353/3/21 19:003/3/21 18:003/3/21 20:00-0.02284434.1444673424674613/3/21 19:003/3/21 18:003/3/21 20:00-0.0227282-2.704650324.144-0.0001161XXXXX
363/3/21 21:003/3/21 20:003/3/21 22:000.003482211.8504674614675823/3/21 21:003/3/21 20:003/3/21 22:000.003308090.396970601.8500.00017412XXXXX
373/3/21 23:003/3/21 22:003/4/21 0:00-0.01441112.1924675824677023/3/21 23:003/3/21 22:003/4/21 0:00-0.0144111-1.714923712.1920
383/4/21 1:003/4/21 0:003/4/21 2:000.004011561.2504677024678213/4/21 1:003/4/21 0:003/4/21 2:000.00380870.4532350601.2500.00020287XXXXX
393/4/21 3:003/4/21 2:003/4/21 4:00-0.00090780.9504678214679423/4/21 3:003/4/21 2:003/4/21 4:00-0.0008072-0.096864900.950-0.0001006XXXXX
403/4/21 5:003/4/21 4:003/4/21 6:00-0.00922311.5614679424680623/4/21 5:003/4/21 4:003/4/21 6:00-0.0092231-1.097545711.5610
413/4/21 7:003/4/21 6:003/4/21 8:00-0.00529371.0914680624681813/4/21 7:003/4/21 6:003/4/21 8:00-0.0053242-0.633581101.0913.0545E-05XXXXX
423/4/21 9:003/4/21 8:003/4/21 10:000.001553962.0304681814683023/4/21 9:003/4/21 8:003/4/21 10:000.001557170.1868608602.030-3.217E-06XXXXX
433/4/21 11:003/4/21 10:003/4/21 12:00-0.00449212.4824683024684223/4/21 11:003/4/21 10:003/4/21 12:00-0.0044921-0.534556802.4820
443/4/21 13:003/4/21 12:003/4/21 14:00-0.01218973.7434684224685413/4/21 13:003/4/21 12:003/4/21 14:00-0.0122006-1.451869313.7431.0917E-05XXXXX
453/4/21 15:003/4/21 14:003/4/21 16:000.009494982.1404685414686623/4/21 15:003/4/21 14:003/4/21 16:000.009590431.1508521902.140-9.546E-05XXXXX
463/4/21 17:003/4/21 16:003/4/21 18:00-0.00109734.0744686624687823/4/21 17:003/4/21 16:003/4/21 18:00-0.0010973-0.130575904.0740
473/4/21 19:003/4/21 18:003/4/21 20:00-0.00040031.3914687824689013/4/21 19:003/4/21 18:003/4/21 20:00-0.0005669-0.067464501.3910.00016666XXXXX
483/4/21 21:003/4/21 20:003/4/21 22:00-0.00200871.5614689014690223/4/21 21:003/4/21 20:003/4/21 22:00-0.0021397-0.25676201.5610.00013097XXXXX
493/4/21 23:003/4/21 22:003/5/21 0:00-0.00842611.5314690224691423/4/21 23:003/4/21 22:003/5/21 0:00-0.0084261-1.002705211.5310
503/5/21 1:003/5/21 0:003/5/21 2:00-0.00329650.8804691424692613/5/21 1:003/5/21 0:003/5/21 2:00-0.0032677-0.38885800.880-2.883E-05XXXXX
513/5/21 3:003/5/21 2:003/5/21 4:00-0.00212821.0914692614693823/5/21 3:003/5/21 2:003/5/21 4:00-0.0021434-0.257209101.0911.5169E-05XXXXX
523/5/21 5:003/5/21 4:003/5/21 6:00-0.00116431.0314693824695023/5/21 5:003/5/21 4:003/5/21 6:00-0.0011643-0.138556601.0310
Sheet2
 
Upvote 0
Lizard_Crimson said:
when there is a discrepency in the index value
Click to expand...
That is the reason behind the method that I suggested. With the array method that you were using originally the index is irrelevant, an array looks at each individual row while indexing looks at a dynamic range of consecutive records.

If column A was in random order an array would work but an indexed range would not, although with the slope function I would imagine that the results would be skewed if the data was unsorted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Generating a list of Dates using nested array inside SEQUENCE function?
Replies
2
Views
279
Cubist
Cubist
L
  • Solved
Using CELL function result as part of range in an AVERAGE formula
Replies
3
Views
351
Fluff
Fluff
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
188
Denny57
D
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
681
felixstraube
F
B
  • Question Question
Sort/Filter based on SUMIF Value
2
Replies
13
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top