Forum Discussion
Help needed to link IF functions into 1 cell
That is what I sort of thought and mentioned in my reply all you have to do is remove that last line of the equation:
=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)This should return the 0,1,2,3,4,5 all on the same row
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)),
...
)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.
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.
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)
