fbpx

Formula Forensics. 009 – Pradhishnair’s Chainage Problem

Share

Facebook
Twitter
LinkedIn

A common Forum question and one that Chandoo has written about a few times is, Does my data overlap with another range?

Last week, Pradhishnair, asked in the Chandoo.org Forums I need to find if the values between range D2:E2 are overlapping in any of the following rages, if yes then where? (may be row number)”

I answered with an array formula:

{=”Overlap Row S.no. = ” &MIN(IF((D3:$D$41<=E2) *(E3:$E$41>=D2) >0, (A3:$A$41)))}

Which returns ether:

Overlap Row S.no. = x (If there is an overlap)

or

Overlap Row S.no. = 0 (If there is no overlap)

So today in Formula Forensics we will take a look at how the above formula works with a worked example.

 

Chainage

As always you can follow along with a worked example. This is a subset of Pradhishnair’s Data to simplify the length of the equations, but otherwise is the same as the original post: Download Here

Pradhishnair’s data consists of a list of chainages. Chainages are measurements of distances from a fixed point and relate to a segment of something. Chainage From is to the closest end of the segment and and Chainage To is to the furthest end of the segment and by default these are in order, that is To is always greater than From.

Using this we can check for overlapping data by simply checking if the From is less than the remaining To’s or the To value is greater than the remaining From’s

This is done for each row compared to the remaining rows of data by the array formula

=”Overlap Row S.no.: ” & MIN(IF((D3:$D$41<=E2) *(E3:$E$41>=D2) >0, (A3:$A$41))) Ctrl Shift enter

We can see that the above equation simply joins

“Overlap Row S.no.: “

To the result of

MIN(IF((D3:$D$41<=E2) *(E3:$E$41>=D2) >0, (A3:$A$41)))

Using the & operator

It will return:

Overlap Row S.no. = 0 if there is no overlap

Or

Overlap Row S.no. = x If there is an overlap.

So the function

MIN(IF((D3:$D$41<=E2) *(E3:$E$41>=D2) >0, (A3:$A$41)))

Is used to return the minimum value of an If() formula which is its only component

The If() statement usually has three components:

=If( Condition, Value if True, Value if False)

In our case, I will evaluate Row 2, Particularly Cell G2, against the remaining entries in the two lists.

=If((D3:$D$41<=E2) *(E3:$E$41>=D2) >0, (A3:$A$41))

Condition:  (D3:$D$41<=E2) *(E3:$E$41>=D2) >0

Value if True:  (A3:$A$41), This is just the S.No. in Column A

Value if False:  Note that the If statement has no Value if it is False, I could put any value there eg: -1 or 0, but Excel evaluates it to 0 anyway so it hasn’t been used.

 

The If Statement says If (D3:$D$41<=E2) *(E3:$E$41>=D2) >0, then return (A3:$A$41)

What the condition is saying is If the To Data in the Current Row, Row 2, is Greater than or equal to the other From Values or the From Data in the Current Row, Row 2, is Less than the other To Value, Then this is True

To check this, in Cell I2, enter  =(D3:$D$41<=E2) *(E3:$E$41>=D2) and evaluate it with F9 instead of Enter

Excel returns ={1;1;0;0;0;0;0;0;0}

Excel is showing us that the first and second entries have overlapping data.

Which we can see if we look at the data

The >0 at the end of the (D3:$D$41<=E2) *(E3:$E$41>=D2) >0, transforms the array of 0’s and 1’s to an array of Falses and Trues

To check this, in I3, enter  =(D3:$D$41<=E2) *(E3:$E$41>=D2)>0 and evaluate it with F9 instead of Enter

Excel returns ={TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Now the If Statement will be True for the first two entries in the array and so will evaluate the Value if True component of the If Formula:  (A3:$A$41)

We can check this

In cell I4 enter

=IF((D3:$D$41<=E2) *(E3:$E$41>=D2) >0, (A3:$A$41)) and press F9 instead of Enter

Excel returns

={2;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Which is the array of S.No’s which match our criteria

Now Min comes in

=MIN({2;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

Min returns the Minimum of these which is 2.

And that is appended to “Overlap Row S.No. = “

To return the answer:  Overlap Row S.No. = 2

Which is the first overlapping row with Row 1

 

Custom Number Formats

You may have noticed that the numbers in Pradhishnair’s worksheet look a bit odd with a + in the middle: eg: D3 displays 142+000

Pradhishnair is using a Custom Number Format of #+000

Select a Cell, eg D2, Press Ctrl 1, Select the Number Tab

If you select these cells and Press F2 Excel will show that the cells value is 142000

But Excel is using the custom Number format of #+000 to display the number with 3 digits after the +

 

Links to Overlapping Data Posts

http://chandoo.org/wp/2010/06/01/date-overlap-formulas/

http://chandoo.org/wp/2011/03/09/ec1-machine-scheduling-in-excel/

 

Download

You can download a copy of the above file and follow along, Download Here.

 

Other Formula Forensics Posts

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic Series:

 

We Need Your Help

I received 3 ideas over the Xmas break from readers, and these will feature in coming weeks.

However I do need more ideas for the rest of 2012 and so I need your help.

 

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post as Luke did in Formula Forensics 003. or like above.

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Hui.

 

ps: I’m traveling to Esperance; Western Australia for a short holiday with the family but have left a neat Formula Forensic for you for next week.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

    You want to learn

    Welcome to Chandoo.org

    Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

    Read my storyFREE Excel tips book

    Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
    Rebekah S
    Reporting Analyst
    Excel formula list - 100+ examples and howto guide for you

    From simple to complex, there is a formula for every occasion. Check out the list now.

    Calendars, invoices, trackers and much more. All free, fun and fantastic.

    Advanced Pivot Table tricks

    Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

    Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

    6 Responses to “Formula Forensics. 009 – Pradhishnair’s Chainage Problem”

    1. Nikki says:

      Thank you Guys for imparting such good knowledge and expertise daily - my brain can't handle a lot of it but you and Chandoo are not only inspirational, but aspirational as well.

      Keep doing such an awesome job

      Regards from sunny South Africa,

      Nikki 🙂

    2. Pradhish Nair says:

      The formula has been a life saver for me..

      thanks again Hui..

    3. JohnC_UK says:

      I love the information on your site. I only came across it a couple of weeks ago.

      I nicked and adapted this example but I made two changes.

      1. I changed the array formula to:

      {=MIN(IF((D3:$D$41 = D2) >0, (A3:$A$41)))}

      so the cell contains the number that overlaps - allowing me to use that elsewhere without the text causing any problems.

      2. I set a custom format of:

      "Overlap Row S.no.: "0;;

      Only those rows with non-zero values will display anything, zero values are blank and there should be no negative values.

      I'm learning a lot from your site and the explanations are the clearest I've ever read.

      Keep up the excellent work!

    4. Jakob Olsen says:

      Hi

      Thank you for this - I have really looked for something like it.
      I have one problem with it though that I can't solve.
         
      My list of entries is a list that people keep adding rows to. And I can't seem to make it work with the new rows except I have to go change the formula everytime a new row is added.
      I have tried using a tableformat in Excel but I can't seem to make that work either.
      Can you please help me?

      Y/Jakob   

    5. rohit says:

      Please Help me for Preparing bar chart with Chainage gap for particular Item in if or condition which i will get answer 1 and i can fill the cell with color by conditional formatting

      4
      1.03a
      Excavation Road
      ChK-JhB
      4450
      5550
      LHS
      33
      71990
      73090

      4
      1.3a
      Excavation Road
      ChK-JhB
      5950
      8050
      LHS
      33
      73490
      75590

      4
      1.3a
      Excavation Road
      ChK-JhB
      8550
      9175
      LHS
      33
      76090
      76715

      4
      1.3a
      Excavation Road
      ChK-JhB
      9450
      9800
      LHS
      33
      76990
      77340

      4
      1.3a
      Excavation Road
      ChK-JhB
      10350
      10590
      LHS
      33
      77890
      78130

      4
      1.3a
      Excavation Road
      ChK-JhB
      11000
      11050
      LHS
      33
      78540
      78590

      4
      1.3a
      Excavation Road
      ChK-JhB
      11235
      11285
      LHS
      33
      78775
      78825

      4
      1.3a
      Excavation Road
      ChK-JhB
      11600
      11860
      LHS
      33
      79140
      79400

      4
      1.3a
      Excavation Road
      ChK-JhB
      11900
      12100
      LHS
      33
      79440
      79640

       
      0+000
      0+100
      0+200
      0+300
      0+400
      0+500
      0+600
      0+700
      0+800
      0+900

      0+000 - 0+090
      0+100 - 0+190
      0+200 - 0+290
      0+300 - 0+390
      0+400 - 0+490
      0+500 - 0+590
      0+600 - 0+690
      0+700 - 0+790
      0+800 - 0+890
      0+900 - 0+990

      0
      10
      20
      30
      40
      50
      60
      70
      80
      90
      100
      110
      120
      130
      140
      150
      160
      170
      180
      190
      200
      210
      220
      230
      240
      250
      260
      270
      280
      290
      300
      310
      320
      330
      340
      350
      360
      370
      380
      390
      400
      410
      420
      430
      440
      450
      460
      470
      480
      490
      500
      510
      520
      530
      540
      550
      560
      570
      580
      590
      600
      610
      620
      630
      640
      650
      660
      670
      680
      690
      700
      710
      720
      730
      740
      750
      760
      770
      780
      790
      800
      810
      820
      830
      840
      850
      860
      870
      880
      890
      900
      910
      920
      930
      940
      950
      960
      970
      980
      990

      28
      EMBANKMENT
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1

      29
      Excavation BA
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1

      34
      Excavation Road
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1

    6. MUDASSIR says:

      HI,
      Thank you for this. Here in this case S.no. 1 is Overlapping with S.no. 2 & S.no. 3 (Cell D2 is Overlapping with E4 and cell E2 is overlapping with D3) but it displayed "Overlap Row S.no.: 2" Instead i need see it as "Overlap Row S.no.: 2,3" Please help me to solve this issue.

    Leave a Reply