Forum Discussion

ExcelNovice365's avatar
ExcelNovice365
Copper Contributor
Oct 11, 2021
Solved

Need Help Please..... need and IF function or a Formula to assist

Formulas to assist snippet below

I'm trying to figure out if Values in K column falls in between column D and E?? and have it present, in comumn F, if the turnout values is present between the upper and lower limit.

Column D being the lower limits for that particular chainage and E the Upper limit. 

 

Now I want a formula to help out but I'm no expert in this, been trying to use If and function but doesn't seem right because there are multiple values of the turn out( columnK)?? 

 

=IF(AND(D2<$K$2,E2>$K$2),"Turnout","")

 

but I want to use all the turn out chainages????? 

 

Please Help.

 

excel
Formulas and Functions
  • edawcj's avatar
    edawcj
    Oct 11, 2021

    ExcelNovice365 -- Since you did not provide a file, I created my own data for illustration purposes. I hope its close enough to your actual data to understand.

     

    I have concluded that you need to re-structure your data such that:

    1. the LIMITS and CHAINAGES tables are combined into a single table eg "TurnoutDecision", and
    2. the CHAINAGES values need to be TRANSPOSED and cast as HEADERS

    Once the above has been completed, you merely need to

    1. reference the VALUE(HEADER) in the CHAINAGES portion of the table, 
    2. compare against the LOCATION 1 & 2 with an IF/AND combo statement,
      throw a "1" where the VALUE(HEADER) meets the BETWEEN conditions for Location 1, Location 2:



      Note we WRAP the HEADER reference in VALUE in order to convert the TEXT format that is forced upon HEADER items into a NUMERAL for SUM purposes. Without the VALUE() function, you'll get nowhere fast.

      Next, the TurnoutTest Column performs a SUM on all CHAINAGES by ROW(s):

      Finally, the "Turnout or Not" column performs an IF on the TurnoutTest value:


    Of course, COL F & G are oversimplified here for the purposes of illustration.
    I challenge you to combine the "TurnoutTest" & "Turnout Or Not" columns into a single column.


    See the attached file.

    Lastly, if you need help TRANSPOSING tables data I suggest referencing this: https://stackoverflow.com/questions/60266169/dynamic-arrays-spill-horizontally

    /C

2 Replies

  • edawcj's avatar
    edawcj
    Brass Contributor
    Oct 11, 2021

    ExcelNovice365 -- Since you did not provide a file, I created my own data for illustration purposes. I hope its close enough to your actual data to understand.

     

    I have concluded that you need to re-structure your data such that:

    1. the LIMITS and CHAINAGES tables are combined into a single table eg "TurnoutDecision", and
    2. the CHAINAGES values need to be TRANSPOSED and cast as HEADERS

    Once the above has been completed, you merely need to

    1. reference the VALUE(HEADER) in the CHAINAGES portion of the table, 
    2. compare against the LOCATION 1 & 2 with an IF/AND combo statement,
      throw a "1" where the VALUE(HEADER) meets the BETWEEN conditions for Location 1, Location 2:



      Note we WRAP the HEADER reference in VALUE in order to convert the TEXT format that is forced upon HEADER items into a NUMERAL for SUM purposes. Without the VALUE() function, you'll get nowhere fast.

      Next, the TurnoutTest Column performs a SUM on all CHAINAGES by ROW(s):

      Finally, the "Turnout or Not" column performs an IF on the TurnoutTest value:


    Of course, COL F & G are oversimplified here for the purposes of illustration.
    I challenge you to combine the "TurnoutTest" & "Turnout Or Not" columns into a single column.


    See the attached file.

    Lastly, if you need help TRANSPOSING tables data I suggest referencing this: https://stackoverflow.com/questions/60266169/dynamic-arrays-spill-horizontally

    /C

Resources