Adaptive x axis spacing on change of y value

Atholl

Active Member
Joined
May 19, 2002
Messages
436
Hi All,

First post in around 17 years :) Stumped here after extensive trawling on the web and thought I might try here.

The plot below shows an xy scatter performance curve for a valve. The solid line comprises around 2000 points and you see it provides a nice smooth curve, particularly where the curve tends to zero on the y axis. The dots comprise the same curve expressed by 50 points at linearly spaced points on the x axis. You can see we don't need so many points on the straight part of the curve to describe it accurately, but as it goes towards zero, we need more points to capture the shape at the lower y values and this is what I'm interested in. I could easily just plot 2000 points for a smooth curve, but don't want to bump up the file size any more. For info, the y axis is a calculated value from the 50 x values. At present I have a formula in there to calculate the linear x spacing as the value at the x intersection changes with different cases.
So the question is: is there any way I can define the x axis points in a bit more of an adaptive way based on the change in y value?

1714032217438.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I did this - if you have 365 it is here - if not, you can sort it out for an older excel (It's just 365 because it's convenient). You can change the number of 'slices' in E1

MrExcelPlayground22.xlsx
ABCDEF
1xyxy50New Data Points
211.49033911.490339
321.489966811.460213
431.4895941581.430618
541.4892212341.400793
651.4888473091.370726
761.4884743811.341227
871.4881014521.311488
981.4877285221.281492
1091.4873545901.251666
11101.4869816561.22202
12111.4866077211.192104
13121.4862337841.162373
14131.4858598461.132353
15141.4854859061.102522
16151.4851119641.072898
17161.48473710211.042966
18171.48436310761.013246
19181.48398911300.983192
20191.48361411820.953357
21201.4832412320.92376
22211.48286512810.893806
23221.4824913280.864099
24231.48211613740.834
25241.48174114180.804156
26251.48136614600.774597
27261.48099115010.74461
28271.48061515400.71492
29281.4802415780.684755
30291.47986516130.655744
31301.47948916480.625389
32311.47911416810.595352
33321.47873817120.565685
34331.47836217410.536449
35341.47798717690.506623
36351.47761117960.476095
37361.47723518210.44597
38371.47685918440.416333
39381.47648218650.387298
40391.47610618850.35746
41401.4757319040.326599
42411.47535319210.296273
43421.47497619360.266667
44431.474619490.238048
45441.47422319610.208167
46451.47384619720.176383
47461.47346919810.145297
48471.47309219880.11547
49481.47271519930.088192
50491.47233819970.057735
51501.4719620000
52511.47158320000
53521.47120520000
54531.47082720000
55541.4704520000
56551.47007220000
57561.46969420000
58571.46931620000
59581.46893820000
60591.46855920000
61601.46818120000
62611.46780320000
63621.46742420000
64631.46704520000
65641.46666720000
66651.46628820000
67661.46590920000
68671.4655320000
69681.46515120000
70691.46477220000
71701.46439220000
72711.46401320000
73721.46363320000
74731.46325420000
75741.46287420000
76751.46249420000
77761.46211420000
78771.46173420000
79781.46135420000
80791.46097420000
81801.46059320000
82811.46021320000
83821.45983320000
84831.45945220000
85841.45907120000
86851.4586920000
87861.4583120000
88871.45792920000
89881.45754720000
90891.45716620000
91901.45678520000
92911.45640320000
93921.45602220000
94931.4556420000
95941.45525920000
96951.45487720000
97961.45449520000
98971.45411320000
99981.45373120000
100991.45334920000
1011001.45296620000
1021011.452584
1031021.452201
1041031.451819
1051041.451436
1061051.451053
1071061.45067
1081071.450287
1091081.449904
1101091.449521
1111101.449138
1121111.448754
1131121.448371
1141131.447987
1151141.447603
1161151.44722
1171161.446836
1181171.446452
1191181.446067
1201191.445683
1211201.445299
1221211.444914
1231221.44453
1241231.444145
1251241.443761
1261251.443376
1271261.442991
1281271.442606
1291281.442221
1301291.441835
1311301.44145
1321311.441064
1331321.440679
1341331.440293
1351341.439907
1361351.439522
1371361.439136
1381371.438749
1391381.438363
1401391.437977
1411401.437591
1421411.437204
1431421.436817
1441431.436431
1451441.436044
1461451.435657
1471461.43527
1481471.434883
1491481.434496
1501491.434108
1511501.433721
1521511.433333
1531521.432946
1541531.432558
1551541.43217
1561551.431782
1571561.431394
1581571.431006
1591581.430618
1601591.430229
1611601.429841
1621611.429452
1631621.429063
1641631.428675
1651641.428286
1661651.427897
1671661.427508
1681671.427118
1691681.426729
1701691.42634
1711701.42595
1721711.42556
1731721.425171
1741731.424781
1751741.424391
1761751.424001
1771761.42361
1781771.42322
1791781.42283
1801791.422439
1811801.422049
1821811.421658
1831821.421267
1841831.420876
1851841.420485
1861851.420094
1871861.419703
1881871.419311
1891881.41892
1901891.418528
1911901.418136
1921911.417745
1931921.417353
1941931.416961
1951941.416569
1961951.416176
1971961.415784
1981971.415392
1991981.414999
2001991.414606
2012001.414214
2022011.413821
2032021.413428
2042031.413035
2052041.412641
2062051.412248
2072061.411855
2082071.411461
2092081.411067
2102091.410674
2112101.41028
2122111.409886
2132121.409492
2142131.409097
2152141.408703
2162151.408309
2172161.407914
2182171.407519
2192181.407125
2202191.40673
2212201.406335
2222211.40594
2232221.405545
2242231.405149
2252241.404754
2262251.404358
2272261.403963
2282271.403567
2292281.403171
2302291.402775
2312301.402379
2322311.401983
2332321.401586
2342331.40119
2352341.400793
2362351.400397
Sheet14
Cell Formulas
RangeFormula
A2:A2001A2=SEQUENCE(2000)
B2:B2001B2=SQRT(2000-A2#)/30
C2:D2C2=A2
C3:C101C3=VALUE(XLOOKUP(D3,$B$2#,$A$2#,"",0))
D3:D101D3=IFNA(INDEX($B$2#,XMATCH($B$2-((MAX($B$2#)-MIN($B$2#))/$E$1)*(ROW(B2)-1),$B$2#,-1),1),0)
Dynamic array formulas.

1714051984361.png

1714051993568.png
 
Upvote 0
Solution
Hi James,

This is great, many thanks for your help. I've implemented it with the original dataset:

1714057643093.png
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

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