first derivative

michaelg2708

New Member
Joined
Apr 11, 2018
Messages
9
how do i get the first derivative of this data? i would like something that ends up like the orange line on the bottom graph if thats possible. i am trying to find the turning points on the graph and i think the first derivative is the best way to go? thanks
1640197506613.png

graph.xlsx
AB
1898-8
289710
389610
489510
589410
689310
78929
88919
989011
1088911
1188811
1288711
1388611
148858
158848
168838
178828
188818
198808
208798
218788
22877-8
23876-8
24875-8
25874-8
26873-14
27872-9
28871-9
29870-9
30869-7
31868-22
32867-21
33866-21
34865-21
35864-20
36863-20
37862-20
38861-20
39860-20
40859-20
41858-20
42857-20
43856-20
44855-20
45854-20
46853-20
47852-20
48851-36
49850-43
50849-43
51848-43
52847-41
53846-41
54845-41
55844-41
56843-41
57842-41
58841-33
59840-36
60839-36
61838-36
62837-36
63836-36
64835-36
65834-36
66833-36
67832-36
68831-36
69830-36
70829-36
71828-36
72827-36
73826-34
74825-34
75824-27
76823-14
77822-14
78821-14
79820-14
80819-14
81818-14
82817-14
83816-14
84815-16
85814-16
86813-16
87812-16
88811-16
89810-16
90809-16
91808-16
92807-16
93806-16
94805-14
95804-44
96803-44
97802-44
98801-44
99800-75
100799-75
101798-58
102797-58
103796-58
104795-58
105794-58
106793-58
107792-58
108791-68
109790-68
110789-66
111788-66
112787-66
113786-66
114785-69
115784-67
116783-67
117782-50
118781-50
119780-50
120779-50
121778-41
122777-92
123776-92
124775-102
125774-102
126773-102
127772-102
128771-97
129770-109
130769-109
131768-109
132767-109
133766-109
134765-109
135764-109
136763-117
137762-114
138761-114
139760-114
140759-114
141758-125
142757-125
143756-125
144755-125
145754-125
146753-125
147752-125
148751-125
149750-127
150749-129
151748-129
152747-126
153746-126
154745-126
155744-132
156743-140
157742-198
158741-215
159740-215
160739-215
161738-215
162737-215
163736-215
164735-215
165734-190
166733-187
167732-190
168731-190
169730-190
170729-190
171728-190
172727-190
173726-190
174725-187
175724-189
176723-189
177722-189
178721-189
179720-187
180719-187
181718-187
182717-187
183716-187
184715-187
185714-187
186713-187
187712-187
188711-187
189710-187
190709-187
191708-187
192707-185
193706-185
194705-185
195704-185
196703-185
197702-185
198701-196
199700-212
200699-212
201698-210
202697-202
203696-202
204695-202
205694-251
206693-251
207692-251
208691-251
209690-251
210689-251
211688-251
212687-251
213686-251
214685-251
215684-251
216683-251
217682-251
218681-251
219680-253
220679-254
221678-233
222677-232
223676-266
224675-257
225674-257
226673-257
227672-257
228671-257
229670-257
230669-257
231668-257
232667-257
233666-257
234665-257
235664-257
236663-279
237662-250
238661-236
239660-236
240659-253
241658-260
242657-263
243656-266
244655-273
245654-277
246653-274
247652-274
248651-272
249650-270
250649-272
251648-276
252647-285
253646-285
254645-285
255644-284
256643-326
257642-327
258641-326
259640-351
260639-351
261638-352
262637-352
263636-352
264635-364
265634-346
266633-363
267632-366
268631-379
269630-383
270629-383
271628-378
272627-379
273626-379
274625-354
275624-349
276623-351
277622-351
278621-323
279620-323
280619-321
281618-301
282617-320
283616-404
284615-376
285614-372
286613-365
287612-362
288611-357
289610-357
290609-367
291608-402
292607-369
293606-369
294605-365
295604-352
296603-352
297602-352
298601-352
299600-353
300599-364
301598-361
302597-358
303596-358
304595-358
305594-356
306593-356
307592-367
308591-361
309590-361
310589-373
311588-373
312587-371
313586-366
314585-384
315584-426
316583-425
317582-423
318581-423
319580-409
320579-409
321578-398
322577-381
323576-381
324575-381
325574-397
326573-392
327572-391
328571-384
329570-391
330569-391
331568-391
332567-428
333566-459
334565-459
335564-459
336563-453
337562-453
338561-453
339560-453
340559-467
341558-427
342557-427
343556-427
344555-427
345554-427
346553-405
347552-405
348551-405
349550-400
350549-397
351548-397
352547-380
353546-379
354545-396
355544-396
356543-396
357542-396
358541-396
359540-396
360539-393
361538-393
362537-371
363536-359
364535-350
365534-350
366533-350
367532-352
368531-352
369530-352
370529-351
371528-348
372527-348
373526-348
374525-350
375524-350
376523-355
377522-355
378521-355
379520-357
380519-359
381518-359
382517-359
383516-360
384515-335
385514-263
386513-235
387512-279
388511-276
389510-259
390509-269
391508-331
392507-330
393506-322
394505-322
395504-322
396503-322
397502-323
398501-324
399500-324
400499-324
401498-324
402497-392
403496-393
404495-393
405494-392
406493-392
407492-392
408491-390
409490-384
410489-409
411488-410
412487-390
413486-388
414485-388
415484-388
416483-388
417482-391
418481-391
419480-391
420479-580
421478-585
422477-610
423476-610
424475-610
425474-610
426473-710
427472-711
428471-709
429470-709
430469-709
431468-709
432467-716
433466-714
434465-714
435464-719
436463-719
437462-719
438461-711
439460-711
440459-751
441458-751
442457-748
443456-748
444455-748
445454-748
446453-748
447452-752
448451-754
449450-754
450449-729
451448-729
452447-729
453446-729
454445-733
455444-737
456443-734
457442-734
458441-734
459440-734
460439-776
461438-765
462437-751
463436-751
464435-750
465434-755
466433-773
467432-777
468431-762
469430-700
470429-700
471428-736
472427-736
473426-717
474425-714
475424-714
476423-717
477422-718
478421-800
479420-789
480419-829
481418-950
482417-969
483416-961
484415-967
485414-967
486413-967
487412-958
488411-930
489410-983
490409-995
491408-1017
492407-1023
493406-1047
494405-1042
495404-1052
496403-1050
497402-1128
498401-1119
499400-1119
500399-1088
501398-1109
502397-1130
503396-1136
504395-1179
505394-1179
506393-1179
507392-1124
508391-1088
509390-1073
510389-1073
511388-1073
512387-1073
513386-1072
514385-1096
515384-1042
516383-1029
517382-1087
518381-1084
519380-1050
520379-1045
521378-1016
522377-1112
523376-1112
524375-1088
525374-1092
526373-1097
527372-1124
528371-1123
529370-1118
530369-1118
531368-1115
532367-1115
533366-1094
534365-1093
535364-1096
536363-1096
537362-1104
538361-1134
539360-1129
540359-1112
541358-1109
542357-1144
543356-1161
544355-1169
545354-1155
546353-1155
547352-1132
548351-1136
549350-1154
550349-1173
551348-1141
552347-1160
553346-1160
554345-1193
555344-1193
556343-1201
557342-1201
558341-1213
559340-1213
560339-1213
561338-1212
562337-1208
563336-1208
564335-1185
565334-1165
566333-1109
567332-1111
568331-1113
569330-1111
570329-1124
571328-1096
572327-1096
573326-1094
574325-1094
575324-1094
576323-1084
577322-1107
578321-1106
579320-1106
580319-1095
581318-1095
582317-1104
583316-1094
584315-1090
585314-1069
586313-1050
587312-992
588311-996
589310-990
590309-991
591308-1031
592307-1031
593306-1031
594305-1031
595304-1020
596303-1021
597302-1019
598301-1024
599300-1018
600299-1049
601298-953
602297-969
603296-964
604295-907
605294-759
606293-733
607292-658
608291-643
609290-651
610289-579
611288-567
612287-513
613286-505
614285-505
615284-507
616283-501
617282-505
618281-479
619280-479
620279-479
621278-470
622277-470
623276-470
624275-403
625274-463
626273-388
627272-424
628271-458
629270-459
630269-369
631268-381
632267-356
633266-366
634265-331
635264-330
636263-402
637262-402
638261-402
639260-459
640259-513
641258-496
642257-461
643256-466
644255-468
645254-459
646253-459
647252-458
648251-493
649250-477
650249-464
651248-518
652247-470
653246-470
654245-470
655244-427
656243-422
657242-327
658241-381
659240-381
660239-426
661238-398
662237-423
663236-454
664235-448
665234-367
666233-367
667232-320
668231-59
669230202
670229223
67122824
672227124
673226127
674225-3
675224-39
676223-70
677222-67
678221-61
679220-119
680219-144
681218-144
682217-141
683216-142
684215-133
685214-131
686213-131
687212-131
688211-129
689210-127
690209-137
691208-147
692207-237
693206-237
694205-268
695204-302
696203-315
697202-308
698201-311
699200-332
700199-325
701198-344
702197-345
703196-357
704195-351
705194-348
706193-364
707192-339
708191-392
709190-406
710189-409
711188-406
712187-424
713186-435
714185-432
715184-513
716183-513
717182-516
718181-516
719180-543
720179-565
721178-569
722177-488
723176-488
724175-488
725174-491
726173-494
727172-496
728171-496
729170-496
730169-481
731168-606
732167-612
733166-692
734165-673
735164-670
736163-670
737162-670
738161-663
739160-667
740159-667
741158-630
742157-630
743156-630
744155-645
745154-655
746153-661
747152-864
748151-809
749150-830
750149-938
751148-938
752147-910
753146-871
754145-851
755144-877
756143-809
757142-810
758141-811
759140-811
760139-811
761138-729
762137-718
763136-718
764135-641
765134-648
766133-673
767132-673
768131-678
769130-675
770129-707
771128-729
772127-766
773126-766
774125-766
775124-795
776123-794
777122-792
778121-797
779120-797
780119-797
781118-705
782117-597
783116-414
784115344
785114247
786113247
787112247
788111297
789110383
790109390
791108409
792107437
793106526
794105633
795104619
796103720
797102822
798101830
799100830
80099804
80198818
80297843
80396891
80495769
80594794
80693772
80792836
80891819
80990815
81089770
81188685
81287682
81386711
81485642
81584553
81683541
81782470
81881581
81980538
82079519
82178517
82277625
82376634
82475661
82574704
82673869
82772921
82871978
82970946
83069976
83168894
83267869
83366901
83465946
83564900
836631037
83762956
83861980
839601051
840591051
841581066
842571017
843561174
844551147
845541219
846531226
847521226
848511097
849501109
850491095
851481080
852471128
853461084
854451084
855441063
856431041
857421056
858411054
859401016
860391074
861381034
862371075
863361097
864351152
865341358
866331440
867321465
868311262
869301066
870291048
871281116
872271153
873261198
87425979
87524924
87623937
87722984
878211009
87920992
88019755
88118823
88217854
88316826
88415766
88514840
88613833
88712850
88811847
88910735
8909663
8918696
8927723
8936764
8945927
8954888
8963908
8972873
8981902
8990953
Sheet1
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
683
Office Version
  1. 365
Platform
  1. Windows
Taking the difference between successive points (column B) gives you the discrete derivative. Anywhere that derivative crosses zero is an inflection point. But your data seems to be all over the place and there are inflection points everywhere.

I filtered out the constant data and found 282 inflections.
Book1
ABCDEFGHI
1898-8.0186XYDeltaInflections:282
289710.061418.0889710.061418.08
389610.0614089510.00074-0.060661
489510.00074-0.060668928.752501-1.248240
589410.00074089010.87252.121
689310.0007408857.905478-2.967021
78928.752501-1.24824877-8.26952-16.1750
88918.7525010873-13.7147-5.445140
989010.87252.12872-8.814554.9001161
1088910.87250869-7.1091.7055440
1188810.87250868-21.769-14.661
1288710.87250867-21.05140.7176291
1388610.87250864-19.92341.1279820
148857.905478-2.96702851-36.3434-16.421
158847.9054780850-43.0764-6.732980
168837.9054780847-40.88142.1951
178827.9054780841-32.75148.130
188817.9054780840-36.2848-3.533431
198807.9054780826-34.08982.1951
208797.9054780824-27.45486.6350
218787.9054780823-14.487712.967150
22877-8.26952-16.175815-16.4877-21
23876-8.269520805-14.29272.1951
24875-8.269520804-43.7627-29.471
25874-8.269520800-74.6235-30.86080
26873-13.7147-5.44514798-58.078516.5451
27872-8.814554.900116794-57.56850.510020
28871-8.814550793-57.55350.0150
29870-8.814550791-67.5535-101
30869-7.1091.705544789-65.84351.711
31868-21.769-14.66788-65.73350.110
32867-21.05140.717629785-68.7385-3.0051
33866-21.05140784-66.50852.231
34865-21.05140782-49.858516.650
35864-19.92341.127982778-40.6379.221480
36863-19.92340777-92.0963-51.45921
37862-19.92340775-102.096-100
38861-19.92340771-97.36964.7266451
39860-19.92340770-108.554-11.18431
Sheet1
Cell Formulas
RangeFormula
I1I1=SUM(H2:H583)
E2:G583E2=FILTER(A1:C899,C1:C899<>0)
C2:C39C2=B2-B1
H3:H39H3=IF(SIGN(G3)<>SIGN(G2),1,0)
Dynamic array formulas.
 

michaelg2708

New Member
Joined
Apr 11, 2018
Messages
9
Thanks for the quick reply. What column would I add on to my chart to get the derivative and would I subtract that new column from column b? I'm not great with excel
 

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
683
Office Version
  1. 365
Platform
  1. Windows
I've got it there in my reply. Column C is the 'derivative' of column B. You can see that C2 = B2-B1. So for each derivative you loose a row of data.

Your data set had a many records where there was no change for 5 or 6 rows. So I filtered that out in Columns E and F - hoping to smooth it out a bit. It didn't help much. You have noisy data.

You might try smoothing a bit, and then taking the derivative. Maybe C10 could be AVERAGE(B1:B10), and then drag that down to the bottom of your data. Then in D11 = C11-C10. Then graph the new C and D.
 

Forum statistics

Threads
1,175,796
Messages
5,899,539
Members
434,782
Latest member
JPolsgrove

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
Top