View original (French)

Overlapping formula in Excel

Solved
dominique.l Posted messages 54 Status Member -  
dominique.l Posted messages 54 Status Member -
Hello everyone,

I need your help, I created a formula that almost works for the overlap, I would like assistance to finalize the formula see example from my file.

https://www.cjoint.com/c/HJemiYsixk8

Thank you

Best regards

Dominique

8 answers

  1.
    didibonf Posted messages 418 Registration date   Status Member Last intervention   96
     
    Hello,
    You can create a link via https://www.cjoint.com/
    0
    1. dominique.l Posted messages 54 Status Member
       
      Thank you for the information.
      0
  2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Hello Dominique.
    • To begin with, you need to remove lines 4 and 5, which prevent the Tri-Filtre tool from functioning.
    • Likewise, lines 1 and 2 should be removed as they serve no purpose.
    • Next, in column C do not enter values, but rather subtract.
    • Finally, your formula is fanciful, as IF(A7<$B$2:B6 ... does not exist! You cannot compare a value to a range with the functions IF, OR, NOT, AND. It should be something like =IF(OR(AND(A7<MIN($B$2:B6);A7>MAX($A$2:A6);AND(A6<MIN($B$3:B7);A6>A7));"Ch";"Non ch")

    --
    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)
    0
  3. dominique.l Posted messages 54 Status Member
     
    Hello Mr. Pentier,

    I am reaching out to you regarding a problem with an Excel formula related to overlapping hours.

    Would it be possible for you to help me?

    https://www.cjoint.com/c/HJfmc0znfD8

    Best regards

    Dominique
    0
    1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      I don't understand what you're doing: I gave you corrections to make; you're sending me a file without any of those corrections!
      What more do you want me to do?
      0
  4. dominique.l Posted messages 54 Status Member
     
    Hello Mr. PENTIER,

    I'm sorry I didn't pay attention to your message, my apologies.

    I'm sending you back the file where I adjusted a parenthesis so that the formula works (Parenthesis error message).

    The formula is applied to the columns and rows, but I think it's not working, and I believe I'm making a mistake in applying it.

    Could you please help me?

    https://www.cjoint.com/c/HJij1IrFEt8

    Thank you very much

    Best regards
    0
  6. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    There are two errors in the file:
    The values in B5 and B13 are incorrect!

    It's good, retirement! Especially in the West Indies...
    Raymond (INSA, AFPA)
    0
    1. dominique.l Posted messages 54 Status Member
       
      Hello,

      Yes, this is intentional; it is to check if the formula works, Mr. Pentier.

      Thank you.
      0
  7. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    So the formula in line 3 is
    =IF(OR(A3>=MAX(B2:B$2),B3<=MIN(A2:A$2)),"Non ch","Ch")
    to be copied down.

    --
    It's good, retirement! Especially in the West Indies...
    Raymond (INSA, AFPA)
    0
  9. dominique.l Posted messages 54 Status Member
     
    Hello Mr. Pentier

    A very big thank you for your help.

    Best regards

    Dominique
    0