Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 16, 2024

Re: Help needed to link IF functions into 1 cell

That's duplication of https://techcommunity.microsoft.com/discussions/excelgeneral/wanting-to-link-multiple-ifandor-functions-into-1-formular/4357819 

As variant

=SWITCH( ROW(),
  2, IF($B2="","",IF(OR(AND($B2<T$1,$H2>T$1),AND($B2>T$1,$B2<U$1)),1,0)),
  3, IF($I2="","14",IF(OR(AND($I2<T$1,$M2>T$1),AND($I2>T$1,$I2<U$1)),2,0)),
  ...
 )

7 Replies

  • MattRaw's avatar
    MattRaw
    Copper Contributor
    Dec 17, 2024

    Thank you for this (and apologies for the duplication. I couldn't find my original post so assumed that it had been removed by moderators for some unknown reason).

     

    So, to get my head around it, to merge all the formulars into one, I need the "=SWITCH(ROW()," before the 1st line, and the follow on with the remaining rows?

     

    That is what I have tried.

    Here is the overall formular I end up with (Note 1: The cells are slightly different from when I originally posted - Note 2: Each line works as desired on their own):

    =SWITCH(ROW(),IF($AW$80="","",IF(OR(AND($AW$80<=CA$2,$BD80>=CA$2),AND($AW$80>=CA$2,$AW$80<=CB$2)),1,0)),IF($BE80="","",IF(OR(AND($BE80<=CA$2,$BI80>=CA$2),AND($BE80>=CA$2,$BE80<=CB$2)),2,0)),IF($BJ80="","",IF(OR(AND($BJ80<=CA$2,$BK80>=CA$2),AND($BJ80>=CA$2,$BJ80<=CB$2)),3,0)),IF($BL80="","",IF(OR(AND($BL80<=CA$2,$BM80>=CA$2),AND($BL80>=CA$2,$BL80<=CB$2)),4,0)),IF(($BM80+31)="","",IF(OR(AND(($BM80+31)<=CA$2,($BM80+59)>=CA$2),AND(($BM80+31)>=CA$2,($BM80+31)<=CB$2)),5,0)))

     

    What I have found is that although I don't get an error in the CELL, the only cells that return numerical values as desired (other than 0s) are the ones that return the number 5.

    None of the CELLs return numbers 1, 2, 3 or 4.

     

    Any Ideas where I have gone wrong?

    Thank you.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 18, 2024

      For the initial sample it could be

      =SWITCH( ROW(),
  2, IF($B$2="","",  IF(OR(AND($B$2<T$1,$H$2>T$1),AND($B$2>T$1,$B$2<U$1)),1,0)),
  3, IF($I$2="","14",IF(OR(AND($I$2<T$1,$M$2>T$1),AND($I$2>T$1,$I$2<U$1)),2,0)),
  4, IF($N$2="","",  IF(OR(AND($N$2<T$1,$O$2>T$1),AND($N$2>T$1,$N$2<U$1)),3,0)),
  5, IF($P$2="","",  IF(OR(AND($P$2<T$1,$Q$2>T$1),AND($P$2>T$1,$P$2<U$1)),4,0)),
  6, IF($R$2="","",  IF(OR(AND($R$2<T$1,($S$2+28)>T$1),AND($R$2>T$1,$R$2<U$1)),5,0)),
  ""
 )

      We need to use absolute references for $B$2, etc. Please check in attached file.

      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Dec 18, 2024

        As a variant:

        =(ROUND(IFERROR(
        (--L$3>$A$4)*
        XMATCH(K$3,HSTACK($A$4,$B$4,$C$4,$D$4,$E$4,$F$4,$G$4,$H$4,$I$4,$J$4),1),0)/2,0)
    =(ROW()-ROW($A$3)) )*(ROW()-ROW($A$3))

        so to explain the above:

        line 2 will check that the next column date is at least after the start of the program

        line 3 will find where the above date falls in the list of program dates

            - I listed the dates cells and used HSTACK because I saw your dates are not in consecutive columns

            - after finding the MATCH it is divided by 2 and rounded up to give 0, 1, 2, etc... for each phase

        line 4 is checking if THIS row corresponds to the output row so you can have the bars on separate lines.  If you want you can remove this part and have the results on the same line and then the conditional formatting would show the phase changes by color alone

        And alternative to copy/paste or fill right and down you can have an array formula that will automatically 'Spill' into all the cells by making the dates a range of columns and the rows a range of rows (example is shown in the attached file)

Resources