Forum Discussion

anupambit1797's avatar
anupambit1797
Steel Contributor
Jul 30, 2025

Charting help needed

Dear Experts, 

                    I have a data as below, 

So , in Column "G" we have 2 Rntis( 21,8,66 & 53714), and I need to plot the time difference for report of each rnti, which is calculated by subtracting the consecutive - SFNs, for that rnti.

Now , the problem here is that in below for example , for the rnti-53714, it's not correct, for example at row-244, my last report for rnti=53714 came at row 241, so the difference should be (821-789) * 10, while in my case it's doing the subtraction in consecutive SFNs, which basically messes up my analysis.

I tried to do something like below from column W to AF, but not sure how to achieve this properly( X axis should be my time, Y axis should be my average-ms and distribution should be based on the RNTI.

Attached is the worksheet..(anything in -ve and above 400 is Outliers and need to be removed) in ms column also.

 

Thanks in Advance!

Br,

Anupam

Charting
excel
Formulas and Functions

2 Replies

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jun 18, 2026

    Here is a formula you can use:

    =IFERROR((A2-XLOOKUP(G2,G$1:G1,A$1:A1,NA(),0,-1))*86400000,"")

    In the image below you can see it highlighted in column I

    NOTE: I used column A and multiplied by 864e5 to convert from time difference to ms so that will get rid of those really large negative numbers each time the SFN rolls over (see row 27 in the image) but later in the data there is a time gap/jump that results in a very large number but that is now more 'wrong' than just using the SFN which had rolled over multiple times in that span.  If you want the formula could even 'screen' those values out.  But if you don't like using the actual time difference then just shift those column A references back to column E.

    Also: a possible 'easy' option would be to just SORT the data by RNTI so that all the same RNTI data is grouped together so your simple formula would work (except for the transition point(s) ).

     

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jun 17, 2026

    You can use a formula that looks up the previous SFN for the same RNTI and helper column to flag invalid values