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.