The Mystery of the "Major Unit"

Sal Paradise · Sep 1, 2011

So for the longest time, I have suffered not knowing how the axis calculation from MS worked. On a current project, I didn't want to use macros (and suffer my not-so-literate users having to figure out how to turn on macros), so I wanted to be able to calculate axis lengths in a formula.

MS has the following 'helpful' article in their knowledge base:
http://support.microsoft.com/kb/214075

If the chart is a 2-D area, column, bar, line or x-y scatter chart, the automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:
yMax + 0.05 * ( yMax - yMin )
Otherwise, the automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

That would be incredibly useful if only they told us how the Major Unit is calculated. Unfortunately they don't.

So I tried to reverse engineer it. Here is what I know:
All major units start with a 1, a 2, or a 5.

That's about all I know for sure. The rest of it gets a bit fuzzy.

Basically, I made some VBA to run through and create a bunch of different min and max values for a bar chart, and had the VBA output the Major Unit from the graph so that I could try to reverse engineer how it got from that minimum and maximum to the Major Unit on the graph.

Here is some sample data (sorry it's in CSV -- I couldn't install the VB Excel Display thing)

Min,Max,MajorUnit
0,1,0.2
0,2,0.5
0,3,0.5
0,4,0.5
0,5,1
0,6,1
0,7,1
0,8,1
0,9,1
0,10,2
0,11,2

The "Min" is the smallest value in the chart, the "Max" is the largest.

First I started working solely with the difference between the minimum and maximum values in the graph.

I got tens of thousands of values, and looked at the ratio between the Major Unit and Min - Max.

The values ranged from .275 to .105 for the ratio. So I figured, "Well, I should just take the next higher value from 1, 2, 5 from the difference multiplied by .105". Once I knew the first number, I could raise it to the appropriate power using the magic of excel formulas.

So here was my first attempt:
Delta: =Max-Min
Ratio: =Delta*.105
NextUnit: =LOOKUP(LEFT(SUBSTITUTE(Ratio,".","")*1,2)/10,{0,2;2,5;5,10})
Exponent: =IF(Ratio>=1,LEN(ROUND(Ratio,0))-1,LEN(ROUNDDOWN(Ratio,0-INT(LOG10(ABS(Ratio)))))-2)
Major Unit: =NextUnit * 10 ^ Exponent

That was working well if I had a minimum (or maximum) of 0. When I went to random numbers, it didn't work as well.

There were two big differences:

The factor of .105 only works if you have a zero as your max or min value
The minimum/maximum value will change based on the knowledgebase article linked above

So I had to update the formula a bit.

First I had to make an Adjusted Maximum and Minimum.

AdjMin: =IF(AND(Min>0,Max>0),IF(ABS(Max)-ABS(Min)>0.1666666666666*ABS(Max),0,Min),Min)
AdjMax: =IF(AND(Min<0,Max<0),IF(ABS(Min)-ABS(Max)>0.1666666666666*ABS(Min),0,Max),Max)

Then I had to update Delta to reflect the changed min/max values:
Delta: =AdjMax - AdjMin

Furthermore, I had to change the factor from 0.105 if one of the min/max values wasn't zero:

Ratio: =IF(OR(AdjMin = 0, AdjMax = 0),0.105,0.11)*Delta

The 0.11 was the minimum value I got when running numbers on graphs with non-zero axes:

Zero Values: 0.105009 to 0.262474
Non-Zero Values: 0.110025 to 0.392896

Using these values, I get a decent success rate (over 98.1%, 367 misses out of 20,000 test values), but there's still something wrong. Some values just don't match, and I think I'm missing something important. There is something hiding. And that's where I want you guys to help.

The exponents have a 100% success rate. The problem is figuring out when the axis will switch from a "1" major unit to a "2" major unit to a "5" major unit.

So I wrote another program to help try to determine where the switches happen:
Code:
```
Sub TestMajorUnits2()
 Dim i, j, intLastValue, intRowCount As Integer
 Dim lngCountUp As Double
 Application.ScreenUpdating = False
 Worksheets("Sheet1").ChartObjects("グラフ 1").Activate
 intRowCount = 1
 intLastValue = 0
 lngCountUp = 0
 
 Worksheets("Sheet1").ChartObjects("グラフ 1").Activate
 For i = 1 To 10000000 Step 1
 With ActiveChart.Axes(xlValue)
 lngCountUp = lngCountUp + 10 ^ -5
 Worksheets("Sheet1").Range("H2").Value = 0
 Worksheets("Sheet1").Range("I2").Value = lngCountUp
 If intLastValue <> .MajorUnit Then
 Worksheets("Sheet1").Range("A1").Offset(intRowCount, 0).Value = 0
 Worksheets("Sheet1").Range("B1").Offset(intRowCount, 0).Value = lngCountUp
 Worksheets("Sheet1").Range("C1").Offset(intRowCount, 0).Value = .MinimumScale
 Worksheets("Sheet1").Range("D1").Offset(intRowCount, 0).Value = .MaximumScale
 Worksheets("Sheet1").Range("E1").Offset(intRowCount, 0).Value = .MajorUnit
 intLastValue = .MajorUnit
 intRowCount = intRowCount + 1
 End If
 End With
 Next i
 Application.ScreenUpdating = True
End Sub
```
Here is the list of values I got first running the program:
Min,Max,Major
0,0.000012,0.000002
0,0.000025,0.000005
0,0.00006,0.00001
0,0.00012,0.00002
0,0.00025,0.00005
0,0.0006,0.0001
0,0.0012,0.0002
0,0.0025,0.0005
0,0.006,0.001
0,0.012,0.002
0,0.025,0.005
0,0.06,0.01
0,0.12,0.02
0,0.25,0.05
0,0.6,0.1
0,1.2,0.2
0,2.5,0.5
0,6,1
0,12,2

It looks like the more numbers I get, the weirder it gets. Here are the ratios it changes over at:
0.2
0.25
0.2
0.2
0.25
0.208333333
0.208333333
0.261780105
0.209643606
0.209863589
0.262467192
0.2099958
0.2099958
0.26249475
0.2099958
0.209998005
0.262498884
0.209999769
0.20999999

That suggests to me that there is some other factor I'm missing. Sometimes we change around .21, and sometimes we change around .262. So What the heck is going on?

If anyone has the brains to figure this out, it would really be appreciated. I either am missing some math, or missing some big piece of the puzzle. The numbers seem like nonsense, which means I think I must be totally missing a piece of that puzzle. Is it some stupid rounding thing? Is it the Excel programmers working to make this impossible to solve?

Please, before I lose my mind!

Sal Paradise · Sep 1, 2011

Here are some of the values I am failing with:
Excel 2003<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Min</td><td style=";">Max</td><td style=";">Major (Actual)</td><td style=";">Adj. Min</td><td style=";">Adj. Max</td><td style=";">Delta</td><td style=";">Ratio</td><td style=";">Next Unit</td><td style=";">10^x</td><td style=";">Major (Calc)</td></tr><tr ><td style="color: #161120;text-align: center;">120</td><td style="text-align: right;;">-651,069 </td><td style="text-align: right;;">-647,802 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">-651,069 </td><td style="text-align: right;;">-647,802 </td><td style="text-align: right;;">3,267 </td><td style="text-align: right;;">359.37</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">238</td><td style="text-align: right;;">-601,218 </td><td style="text-align: right;;">-597,353 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">-601,218 </td><td style="text-align: right;;">-597,353 </td><td style="text-align: right;;">3,865 </td><td style="text-align: right;;">425.15</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">268</td><td style="text-align: right;;">-55,860 </td><td style="text-align: right;;">-51,802 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">-55,860 </td><td style="text-align: right;;">-51,802 </td><td style="text-align: right;;">4,058 </td><td style="text-align: right;;">446.38</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">353</td><td style="text-align: right;;">-343,286 </td><td style="text-align: right;;">-339,094 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">-343,286 </td><td style="text-align: right;;">-339,094 </td><td style="text-align: right;;">4,192 </td><td style="text-align: right;;">461.12</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">499</td><td style="text-align: right;;">-348,935 </td><td style="text-align: right;;">-344,569 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">-348,935 </td><td style="text-align: right;;">-344,569 </td><td style="text-align: right;;">4,366 </td><td style="text-align: right;;">480.26</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">531</td><td style="text-align: right;;">-435,165 </td><td style="text-align: right;;">-430,681 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">-435,165 </td><td style="text-align: right;;">-430,681 </td><td style="text-align: right;;">4,484 </td><td style="text-align: right;;">493.24</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">586</td><td style="text-align: right;;">987,730 </td><td style="text-align: right;;">992,275 </td><td style="text-align: right;;">1,000 </td><td style="text-align: right;;">987,730 </td><td style="text-align: right;;">992,275 </td><td style="text-align: right;;">4,545 </td><td style="text-align: right;;">499.95</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">500 </td></tr><tr ><td style="color: #161120;text-align: center;">661</td><td style="text-align: right;;">-668,854 </td><td style="text-align: right;;">-636,740 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-668,854 </td><td style="text-align: right;;">-636,740 </td><td style="text-align: right;;">32,114 </td><td style="text-align: right;;">3532.54</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">864</td><td style="text-align: right;;">-721,258 </td><td style="text-align: right;;">-689,126 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-721,258 </td><td style="text-align: right;;">-689,126 </td><td style="text-align: right;;">32,132 </td><td style="text-align: right;;">3534.52</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1126</td><td style="text-align: right;;">700,091 </td><td style="text-align: right;;">732,938 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">700,091 </td><td style="text-align: right;;">732,938 </td><td style="text-align: right;;">32,847 </td><td style="text-align: right;;">3613.17</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1358</td><td style="text-align: right;;">703,703 </td><td style="text-align: right;;">736,593 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">703,703 </td><td style="text-align: right;;">736,593 </td><td style="text-align: right;;">32,890 </td><td style="text-align: right;;">3617.9</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1396</td><td style="text-align: right;;">-606,202 </td><td style="text-align: right;;">-573,201 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-606,202 </td><td style="text-align: right;;">-573,201 </td><td style="text-align: right;;">33,001 </td><td style="text-align: right;;">3630.11</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1421</td><td style="text-align: right;;">-369,527 </td><td style="text-align: right;;">-336,484 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-369,527 </td><td style="text-align: right;;">-336,484 </td><td style="text-align: right;;">33,043 </td><td style="text-align: right;;">3634.73</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1526</td><td style="text-align: right;;">736,325 </td><td style="text-align: right;;">769,503 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">736,325 </td><td style="text-align: right;;">769,503 </td><td style="text-align: right;;">33,178 </td><td style="text-align: right;;">3649.58</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1579</td><td style="text-align: right;;">917,055 </td><td style="text-align: right;;">950,287 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">917,055 </td><td style="text-align: right;;">950,287 </td><td style="text-align: right;;">33,232 </td><td style="text-align: right;;">3655.52</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1662</td><td style="text-align: right;;">882,270 </td><td style="text-align: right;;">915,637 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">882,270 </td><td style="text-align: right;;">915,637 </td><td style="text-align: right;;">33,367 </td><td style="text-align: right;;">3670.37</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1689</td><td style="text-align: right;;">737,337 </td><td style="text-align: right;;">770,897 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">737,337 </td><td style="text-align: right;;">770,897 </td><td style="text-align: right;;">33,560 </td><td style="text-align: right;;">3691.6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1709</td><td style="text-align: right;;">-347,881 </td><td style="text-align: right;;">-314,320 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-347,881 </td><td style="text-align: right;;">-314,320 </td><td style="text-align: right;;">33,561 </td><td style="text-align: right;;">3691.71</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1770</td><td style="text-align: right;;">832,004 </td><td style="text-align: right;;">865,626 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">832,004 </td><td style="text-align: right;;">865,626 </td><td style="text-align: right;;">33,622 </td><td style="text-align: right;;">3698.42</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1790</td><td style="text-align: right;;">-890,580 </td><td style="text-align: right;;">-856,537 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-890,580 </td><td style="text-align: right;;">-856,537 </td><td style="text-align: right;;">34,043 </td><td style="text-align: right;;">3744.73</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1805</td><td style="text-align: right;;">-645,337 </td><td style="text-align: right;;">-611,135 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-645,337 </td><td style="text-align: right;;">-611,135 </td><td style="text-align: right;;">34,202 </td><td style="text-align: right;;">3762.22</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">1978</td><td style="text-align: right;;">-319,365 </td><td style="text-align: right;;">-284,806 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-319,365 </td><td style="text-align: right;;">-284,806 </td><td style="text-align: right;;">34,559 </td><td style="text-align: right;;">3801.49</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2003</td><td style="text-align: right;;">-907,752 </td><td style="text-align: right;;">-872,855 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-907,752 </td><td style="text-align: right;;">-872,855 </td><td style="text-align: right;;">34,897 </td><td style="text-align: right;;">3838.67</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2275</td><td style="text-align: right;;">-692,397 </td><td style="text-align: right;;">-657,302 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-692,397 </td><td style="text-align: right;;">-657,302 </td><td style="text-align: right;;">35,095 </td><td style="text-align: right;;">3860.45</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2420</td><td style="text-align: right;;">-957,751 </td><td style="text-align: right;;">-922,294 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-957,751 </td><td style="text-align: right;;">-922,294 </td><td style="text-align: right;;">35,457 </td><td style="text-align: right;;">3900.27</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2421</td><td style="text-align: right;;">-733,050 </td><td style="text-align: right;;">-697,447 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-733,050 </td><td style="text-align: right;;">-697,447 </td><td style="text-align: right;;">35,603 </td><td style="text-align: right;;">3916.33</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2614</td><td style="text-align: right;;">632,413 </td><td style="text-align: right;;">668,041 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">632,413 </td><td style="text-align: right;;">668,041 </td><td style="text-align: right;;">35,628 </td><td style="text-align: right;;">3919.08</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2617</td><td style="text-align: right;;">944,243 </td><td style="text-align: right;;">979,937 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">944,243 </td><td style="text-align: right;;">979,937 </td><td style="text-align: right;;">35,694 </td><td style="text-align: right;;">3926.34</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2619</td><td style="text-align: right;;">498,026 </td><td style="text-align: right;;">534,098 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">498,026 </td><td style="text-align: right;;">534,098 </td><td style="text-align: right;;">36,072 </td><td style="text-align: right;;">3967.92</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2625</td><td style="text-align: right;;">-703,582 </td><td style="text-align: right;;">-667,401 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-703,582 </td><td style="text-align: right;;">-667,401 </td><td style="text-align: right;;">36,181 </td><td style="text-align: right;;">3979.91</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2632</td><td style="text-align: right;;">586,020 </td><td style="text-align: right;;">622,228 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">586,020 </td><td style="text-align: right;;">622,228 </td><td style="text-align: right;;">36,208 </td><td style="text-align: right;;">3982.88</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2646</td><td style="text-align: right;;">547,022 </td><td style="text-align: right;;">583,463 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">547,022 </td><td style="text-align: right;;">583,463 </td><td style="text-align: right;;">36,441 </td><td style="text-align: right;;">4008.51</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2647</td><td style="text-align: right;;">-782,160 </td><td style="text-align: right;;">-745,623 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-782,160 </td><td style="text-align: right;;">-745,623 </td><td style="text-align: right;;">36,537 </td><td style="text-align: right;;">4019.07</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2656</td><td style="text-align: right;;">912,868 </td><td style="text-align: right;;">949,918 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">912,868 </td><td style="text-align: right;;">949,918 </td><td style="text-align: right;;">37,050 </td><td style="text-align: right;;">4075.5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2657</td><td style="text-align: right;;">-900,756 </td><td style="text-align: right;;">-863,512 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-900,756 </td><td style="text-align: right;;">-863,512 </td><td style="text-align: right;;">37,244 </td><td style="text-align: right;;">4096.84</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2659</td><td style="text-align: right;;">-516,518 </td><td style="text-align: right;;">-479,050 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-516,518 </td><td style="text-align: right;;">-479,050 </td><td style="text-align: right;;">37,468 </td><td style="text-align: right;;">4121.48</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2661</td><td style="text-align: right;;">-981,680 </td><td style="text-align: right;;">-943,895 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-981,680 </td><td style="text-align: right;;">-943,895 </td><td style="text-align: right;;">37,785 </td><td style="text-align: right;;">4156.35</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2670</td><td style="text-align: right;;">561,628 </td><td style="text-align: right;;">599,505 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">561,628 </td><td style="text-align: right;;">599,505 </td><td style="text-align: right;;">37,877 </td><td style="text-align: right;;">4166.47</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2678</td><td style="text-align: right;;">192,303 </td><td style="text-align: right;;">230,372 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">192,303 </td><td style="text-align: right;;">230,372 </td><td style="text-align: right;;">38,069 </td><td style="text-align: right;;">4187.59</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2683</td><td style="text-align: right;;">-934,128 </td><td style="text-align: right;;">-895,453 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-934,128 </td><td style="text-align: right;;">-895,453 </td><td style="text-align: right;;">38,675 </td><td style="text-align: right;;">4254.25</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2684</td><td style="text-align: right;;">-835,474 </td><td style="text-align: right;;">-796,762 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-835,474 </td><td style="text-align: right;;">-796,762 </td><td style="text-align: right;;">38,712 </td><td style="text-align: right;;">4258.32</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2694</td><td style="text-align: right;;">-440,141 </td><td style="text-align: right;;">-401,412 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-440,141 </td><td style="text-align: right;;">-401,412 </td><td style="text-align: right;;">38,729 </td><td style="text-align: right;;">4260.19</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2698</td><td style="text-align: right;;">292,594 </td><td style="text-align: right;;">331,473 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">292,594 </td><td style="text-align: right;;">331,473 </td><td style="text-align: right;;">38,879 </td><td style="text-align: right;;">4276.69</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2701</td><td style="text-align: right;;">-951,614 </td><td style="text-align: right;;">-911,970 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-951,614 </td><td style="text-align: right;;">-911,970 </td><td style="text-align: right;;">39,644 </td><td style="text-align: right;;">4360.84</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2706</td><td style="text-align: right;;">600,150 </td><td style="text-align: right;;">639,893 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">600,150 </td><td style="text-align: right;;">639,893 </td><td style="text-align: right;;">39,743 </td><td style="text-align: right;;">4371.73</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2709</td><td style="text-align: right;;">-674,386 </td><td style="text-align: right;;">-634,605 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-674,386 </td><td style="text-align: right;;">-634,605 </td><td style="text-align: right;;">39,781 </td><td style="text-align: right;;">4375.91</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2715</td><td style="text-align: right;;">798,019 </td><td style="text-align: right;;">838,415 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">798,019 </td><td style="text-align: right;;">838,415 </td><td style="text-align: right;;">40,396 </td><td style="text-align: right;;">4443.56</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2720</td><td style="text-align: right;;">764,402 </td><td style="text-align: right;;">804,880 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">764,402 </td><td style="text-align: right;;">804,880 </td><td style="text-align: right;;">40,478 </td><td style="text-align: right;;">4452.58</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2722</td><td style="text-align: right;;">372,442 </td><td style="text-align: right;;">413,929 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">372,442 </td><td style="text-align: right;;">413,929 </td><td style="text-align: right;;">41,487 </td><td style="text-align: right;;">4563.57</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2727</td><td style="text-align: right;;">978,789 </td><td style="text-align: right;;">1,020,366 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">978,789 </td><td style="text-align: right;;">1,020,366 </td><td style="text-align: right;;">41,577 </td><td style="text-align: right;;">4573.47</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2755</td><td style="text-align: right;;">589,573 </td><td style="text-align: right;;">631,324 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">589,573 </td><td style="text-align: right;;">631,324 </td><td style="text-align: right;;">41,751 </td><td style="text-align: right;;">4592.61</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2785</td><td style="text-align: right;;">-641,917 </td><td style="text-align: right;;">-599,270 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-641,917 </td><td style="text-align: right;;">-599,270 </td><td style="text-align: right;;">42,647 </td><td style="text-align: right;;">4691.17</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2809</td><td style="text-align: right;;">713,810 </td><td style="text-align: right;;">756,471 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">713,810 </td><td style="text-align: right;;">756,471 </td><td style="text-align: right;;">42,661 </td><td style="text-align: right;;">4692.71</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">2896</td><td style="text-align: right;;">-664,459 </td><td style="text-align: right;;">-621,372 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-664,459 </td><td style="text-align: right;;">-621,372 </td><td style="text-align: right;;">43,087 </td><td style="text-align: right;;">4739.57</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">3130</td><td style="text-align: right;;">-544,260 </td><td style="text-align: right;;">-501,172 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-544,260 </td><td style="text-align: right;;">-501,172 </td><td style="text-align: right;;">43,088 </td><td style="text-align: right;;">4739.68</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">3218</td><td style="text-align: right;;">-353,089 </td><td style="text-align: right;;">-309,821 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-353,089 </td><td style="text-align: right;;">-309,821 </td><td style="text-align: right;;">43,268 </td><td style="text-align: right;;">4759.48</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">3244</td><td style="text-align: right;;">-447,746 </td><td style="text-align: right;;">-403,914 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-447,746 </td><td style="text-align: right;;">-403,914 </td><td style="text-align: right;;">43,832 </td><td style="text-align: right;;">4821.52</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">3247</td><td style="text-align: right;;">-878,575 </td><td style="text-align: right;;">-834,630 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-878,575 </td><td style="text-align: right;;">-834,630 </td><td style="text-align: right;;">43,945 </td><td style="text-align: right;;">4833.95</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">3274</td><td style="text-align: right;;">562,427 </td><td style="text-align: right;;">606,592 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">562,427 </td><td style="text-align: right;;">606,592 </td><td style="text-align: right;;">44,165 </td><td style="text-align: right;;">4858.15</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">3356</td><td style="text-align: right;;">-325,371 </td><td style="text-align: right;;">-280,796 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-325,371 </td><td style="text-align: right;;">-280,796 </td><td style="text-align: right;;">44,575 </td><td style="text-align: right;;">4903.25</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">4744</td><td style="text-align: right;;">948,487 </td><td style="text-align: right;;">993,429 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">948,487 </td><td style="text-align: right;;">993,429 </td><td style="text-align: right;;">44,942 </td><td style="text-align: right;;">4943.62</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">6023</td><td style="text-align: right;;">-516,906 </td><td style="text-align: right;;">-471,618 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-516,906 </td><td style="text-align: right;;">-471,618 </td><td style="text-align: right;;">45,288 </td><td style="text-align: right;;">4981.68</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">6488</td><td style="text-align: right;;">-724,953 </td><td style="text-align: right;;">-679,544 </td><td style="text-align: right;;">10,000 </td><td style="text-align: right;;">-724,953 </td><td style="text-align: right;;">-679,544 </td><td style="text-align: right;;">45,409 </td><td style="text-align: right;;">4994.99</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5,000 </td></tr><tr ><td style="color: #161120;text-align: center;">6884</td><td style="text-align: right;;">-454,987 </td><td style="text-align: right;;">-217,756 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-454,987 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">454,987 </td><td style="text-align: right;;">50048.57</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">7506</td><td style="text-align: right;;">-455,310 </td><td style="text-align: right;;">-169,732 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-455,310 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">455,310 </td><td style="text-align: right;;">50084.1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">7761</td><td style="text-align: right;;">-455,997 </td><td style="text-align: right;;">-58,273 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-455,997 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">455,997 </td><td style="text-align: right;;">50159.67</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">7827</td><td style="text-align: right;;">-456,926 </td><td style="text-align: right;;">-328,742 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-456,926 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">456,926 </td><td style="text-align: right;;">50261.86</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">7833</td><td style="text-align: right;;">-457,601 </td><td style="text-align: right;;">-193,258 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-457,601 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">457,601 </td><td style="text-align: right;;">50336.11</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">7947</td><td style="text-align: right;;">-458,037 </td><td style="text-align: right;;">-101,185 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-458,037 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">458,037 </td><td style="text-align: right;;">50384.07</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">7966</td><td style="text-align: right;;">-459,469 </td><td style="text-align: right;;">-239,814 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-459,469 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">459,469 </td><td style="text-align: right;;">50541.59</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8010</td><td style="text-align: right;;">-459,847 </td><td style="text-align: right;;">-140,703 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-459,847 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">459,847 </td><td style="text-align: right;;">50583.17</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8156</td><td style="text-align: right;;">-460,928 </td><td style="text-align: right;;">-85,986 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-460,928 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">460,928 </td><td style="text-align: right;;">50702.08</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8477</td><td style="text-align: right;;">-462,202 </td><td style="text-align: right;;">-301,324 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-462,202 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">462,202 </td><td style="text-align: right;;">50842.22</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8499</td><td style="text-align: right;;">-462,514 </td><td style="text-align: right;;">-38,514 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-462,514 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">462,514 </td><td style="text-align: right;;">50876.54</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8554</td><td style="text-align: right;;">-462,928 </td><td style="text-align: right;;">-252,034 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-462,928 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">462,928 </td><td style="text-align: right;;">50922.08</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8679</td><td style="text-align: right;;">-465,617 </td><td style="text-align: right;;">-86,570 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-465,617 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">465,617 </td><td style="text-align: right;;">51217.87</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8685</td><td style="text-align: right;;">-467,628 </td><td style="text-align: right;;">-150,073 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-467,628 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">467,628 </td><td style="text-align: right;;">51439.08</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8834</td><td style="text-align: right;;">-468,428 </td><td style="text-align: right;;">-370,531 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-468,428 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">468,428 </td><td style="text-align: right;;">51527.08</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">8998</td><td style="text-align: right;;">-468,562 </td><td style="text-align: right;;">-152,075 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-468,562 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">468,562 </td><td style="text-align: right;;">51541.82</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9160</td><td style="text-align: right;;">-468,562 </td><td style="text-align: right;;">-24,940 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-468,562 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">468,562 </td><td style="text-align: right;;">51541.82</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9424</td><td style="text-align: right;;">-470,029 </td><td style="text-align: right;;">-199,951 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-470,029 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">470,029 </td><td style="text-align: right;;">51703.19</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9577</td><td style="text-align: right;;">-470,491 </td><td style="text-align: right;;">-100,577 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-470,491 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">470,491 </td><td style="text-align: right;;">51754.01</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9599</td><td style="text-align: right;;">-473,189 </td><td style="text-align: right;;">-295,140 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-473,189 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">473,189 </td><td style="text-align: right;;">52050.79</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9742</td><td style="text-align: right;;">-474,314 </td><td style="text-align: right;;">-219,093 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-474,314 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">474,314 </td><td style="text-align: right;;">52174.54</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9775</td><td style="text-align: right;;">-475,184 </td><td style="text-align: right;;">-314,585 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-475,184 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">475,184 </td><td style="text-align: right;;">52270.24</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9912</td><td style="text-align: right;;">-475,509 </td><td style="text-align: right;;">-107,232 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-475,509 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">475,509 </td><td style="text-align: right;;">52305.99</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr><tr ><td style="color: #161120;text-align: center;">9956</td><td style="text-align: right;;">-476,059 </td><td style="text-align: right;;">-366,321 </td><td style="text-align: right;;">50,000 </td><td style="text-align: right;;">-476,059 </td><td style="text-align: right;;">0 </td><td style="text-align: right;;">476,059 </td><td style="text-align: right;;">52366.49</td><td style="text-align: right;;">10</td><td style="text-align: right;;">4</td><td style="text-align: right;;">100,000 </td></tr></tbody></table>Sheet4 <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D120</th><td style="text-align:left">=IF(AND(A120>0,B120>0),IF(ABS(B120)-ABS(A120)>0.1666666666666*ABS(B120),0,A120),A120)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E120</th><td style="text-align:left">=IF(AND(A120<0,B120<0),IF(ABS(A120)-ABS(B120)>0.1666666666666*ABS(A120),0,B120),B120)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F120</th><td style="text-align:left">=E120-D120</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G120</th><td style="text-align:left">=IF(MIN(D120:E120)=0,0.105,0.11)*F120</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H120</th><td style="text-align:left">=LOOKUP(LEFT(SUBSTITUTE(G120,".","")*1,2)/10,{0,2;2,5;5,10})</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I120</th><td style="text-align:left">=IF(ROUNDDOWN(G120,0-INT(LOG10(ABS(G120))))>=1,LEN(ROUNDDOWN(ROUNDDOWN(G120,0-INT(LOG10(ABS(G120)))),0))-1,LEN(ROUNDDOWN(G120,0-INT(LOG10(ABS(G120))))*1000000)-LEN(ROUNDDOWN(G120,0-INT(LOG10(ABS(G120)))))-4)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J120</th><td style="text-align:left">=H120*10^I120</td></tr></tbody></table></td></tr></table>

shg · Sep 1, 2011

It's a mystery to me. Excel's proclivity to include zero within the chart range is equally annoying.

A few years ago, I was plotting data that represented actually geometry (framing around windows), and did a routine to make the axes have equal dimensions. Andy Pope was kind enough to put it on his web site at http://andypope.info/charts/SetSquareAxis.htm.

Sal Paradise · Sep 1, 2011

It's really easy to do with VBA -- I have no problem with that. The problem is trying to do it with formulas. Generally, I do Peltier-esque chart tricks that require two axes which automatically rescale if I don't fix them to 0-1x/0-1y. So I need to be able to calculate what % of the total chart height is required for labels and other goodies.

In order to know the chart height, I need to know the min/max of the chart in question, and to do that I need to be able to calculate the Major Unit. Unfortunately, nobody has found a way to do it yet. (I'm so close! 98.2% isn't bad...)

I know there are some geniuses on this forum who are better with numbers than I am. There are tons of patterns above that may pop out to someone. I may just be missing something. I'm hoping beyond hope that someone out there can take that next step so that I can solve this mystery (it is driving me batty and preventing me from making a nice polished sheet for my job).

Jon Peltier · Sep 2, 2011

There is an algorithm I use based mostly upon that presented by Stephen Bullen, in Chapter 15 of Professional Excel Development (incidentally the absolute best book if you're trying to make a career of Excel development). Stephen's algorithm is done in VBA, but it's not a big deal to convert it to worksheet formulas, though you'll need one set of such formulas per axis. Fortunately Chapter 15 is one of the sample chapters that you can download. This link is to the table of contents to the book:

Professional Excel Development

See if you can find the error in Stephen's code (signes in one formula are switched).

This algorithm calculates axis scale parameters which are somewhat nicer than those Excel chooses.

I doubt anybody could possibly mimic Excel's own automatic major unit calculations. It varies not just on Min and Max, but also on various parameters including but not limited to plot area size, monitor resolution, window zoom factor, and axis tick formatting, including tick axis label font size.

TJSartain · Oct 2, 2016

Jon Peltier said:
There is an algorithm I use based mostly upon that presented by Stephen Bullen, in Chapter 15 of Professional Excel Development (incidentally the absolute best book if you're trying to make a career of Excel development).

Here's my algorithm; also based on Stephen Bullen's example. I think it's a tad cleaner. And instead of the 1, 2, 5 sequence, I use 1, 2.5, 5. That way I get major units of 10, 25, 50, 100, 250, 500, etc. As others have pointed out, there are other considerations in the final analysis including axis height and font sizes that may necessitate a higher unit - or at least less labelling.

Input: min and max of data range
Output: min and max of axis, major unit scale

if max < min,

swap max, min

else if max = min,

increase max by 1%
decrease min by 1%

let padding = 1% of (max - min)

for both min and max,

if positive, add padding
if negative, subtract padding

if min and max are both zero,

let max = 1

let power = log(max - min) / log(10)
let scale = 10 ^ (power - int(power))

if scale <= 2.5 let scale = .25
else if scale <= 5 let scale = .5
else if scale <= 7.5 let scale = 1
else let scale = 2.5

multiply scale by 10 ^ int(power)

return { min: scale * floor(min / scale),
max: scale * ceil(max / scale),
scale: scale}

The Mystery of the "Major Unit"

Sal Paradise

Well-known Member

Excel Facts

Sal Paradise

Well-known Member

shg

MrExcel MVP

Sal Paradise

Well-known Member

Jon Peltier

MrExcel MVP

TJSartain

New Member

Similar threads

Forum statistics

Share this page

The Mystery of the "Major Unit"

Sal Paradise

Well-known Member

Excel Facts

Sal Paradise

Well-known Member

shg

MrExcel MVP

Sal Paradise

Well-known Member

Jon Peltier

MrExcel MVP

TJSartain

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock