Finding highest and lowest number between to key numbers

I am trying to find the highest and lowest numbers between 2 key numbers. In my example the key number is 99999. Below you can see some measurement data collected.

From there I am trying to calculate the difference. I am trying to do this automatically because there is a lot of numbers to go through. I believe a macro is needed. I have written some simple ones, but nothing this complicated.

Sometimes the key number is missing, in that case I want to wait till the next key number comes up... and start again from that key number till the next. If the key number is missing then the numbers will jump in magnitude, for example 704,690,679,686 then to 1025.

Any help would be very appreciated.

TIA

Data below



99999
1036
999
980
960
946
925
912
890
879
862
842
822
810
790
773
755
740
723
704
690
679
686
99999
1025
998
976
960
935
924
903
887
869
857
835
818
798
777
768
748
731
718
701
681
680
980
99999
1013
993
967
954
938
915
899
880
861
850
833
812
797
779
770
743
725
716
700
682
683
706
99999

The sample data you gave is all 23 rows in height (the top one being your "key"). If all your data is set up like this and it starts in Cell A1 you can do this:

Cell B2:
=MAX(A2:A23)

Cell C2:
=MIN(A2:A23)

Select Cells A2:C24
Click the bottom right of this selection (your cursor should turn into a black plus sign instead of a white one) and drag down as far as you have data.

If this is not the case, you can use pivot tables. Pivot tables can give you Min's and Max's. I attached a sample workbook using pivot tables and a couple relatively simple formulas to do this.