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 overtime equals one day of vacation.  The formula looks at counted time and will mark each "accumulated" vacation day with an X when it reaches integers of 7.  Since the values of accumulated time are not whole numbers, the formula has to take that into consideration and not lose time.

 

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 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