Forum Discussion

Renee Schweke's avatar
Renee Schweke
Copper Contributor
Jun 14, 2018

need help with a formula to place an X in a cell when values in another column reach multiples of 7

I am working on a timekeeping worksheet that keeps track of anticipated vacation time.  For each 7 hours of overtime worked, the time is kept in a "bank" to be used for vacation days.  7 hours of ove...
Formulas & Functions
Need Help
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jun 14, 2018

Hi Renee,

 

Please try this formula in column H.

=IF(OR(D1>=7,D1>=14,D1>=21),"X","")

 

I hope I understand you well!

Regards

  • Renee Schweke's avatar
    Renee Schweke
    Copper Contributor
    Jun 14, 2018

    It did not quite work but it got me playing !!! :-)  

    I also changed it to D3, since D3 is the first cell with a numerical value (D1 is a title in my example).
    I tried a range, D3:D10

    =IF(OR(D3:D10>=7,D3:D10>=14,D3>=21),"X","")

    It's not working :-(

     

    I am trying to get it to simply put an "X" in each cell in Column H to show each time a multiple of 7 is reached in column D.

    Cell H3 is first.  7  = X in H3.

    14  = X in H4.

    21 =  X in H5.

    28 = X in H6.

    35 = X in H7.

     

    But we can't look at whole numbers because it skips from (for example) 6.75 to 7.75 in cells D7 and D8.  The .75 can not be lost.

     

    I hope that makes sense :-)

     

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Jun 15, 2018

      Hi Renee,

       

      Sorry about the late reply!

       

      I think I figured out what exactly you're trying to do.

       

      So please try this formula:

      =IF(IF((SUMPRODUCT(OR($D$3:$D$10>=21)+OR($D$3:$D$10>=14)+OR($D$3:$D$10>=7)+OR($D$3:$D$10>=28)+OR($D$3:$D$10>=35)))-ROW(A1)+0.5<0,0,1),"X","")

       

      Try to delete 40 in cell D10, and see how it works!

       

      If you want the result to be in a single cell, you can depend on this:

      =REPT("X",SUMPRODUCT(OR($D$3:$D$10>=21)+OR($D$3:$D$10>=14)+OR($D$3:$D$10>=7)+OR($D$3:$D$10>=28)+OR($D$3:$D$10>=35)))

       

      I know that the formulas are quite complicated, but I didn't figure out a solution easier than this.

       

      However, please find all this in the attached file.

      Regards

      • Renee Schweke's avatar
        Renee Schweke
        Copper Contributor
        Jun 15, 2018

         Hi!!

        Thank you for this.  I tried the formula and it works in H3, but it repeats in every other cell H4 through H7.  But there should be no X in H4 until the values in D3 to D9 reach 14; No X in H5 until the values in D reach 21, No X in H6 until the values in D reach 28, and No X in H7 until the values in D reach 35. 

        I played around with your formula and I can't figure it out either.  Uch!  :-)

         

  • Renee Schweke's avatar
    Renee Schweke
    Copper Contributor
    Jun 14, 2018
    Thank you! I will give this a try.
    Will I be able to carry the formula down column i? So that the hours in column D can go higher than 21 (and there will be more than 3 "X" 's?
    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Jun 14, 2018

      Hi Renee,

       

      Please note that formula returns just single X if values in column D go higher than 7 or 14 or 21.

      If you want it to return more that one X based on the number, so please try this instead:

      =IF(D1>=21,"XXX",IF(D1>=14,"XX",IF(D1>=7,"X","")))

       

      It will give you a result like this:

       

      Please test it, and tell me what you think.

       

      And if you want to adjust the formula so that it will return more than XXX, please provide more details about that because the above formula will return XXX if the value in column D is greater than or equal to 21.

       

      Regards

Resources