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

Copper Contributor

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.

Excel Photo.JPG

 

9 Replies

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

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?

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:

Nested IF.png

 

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

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

 

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","")

Multiple X.png

 

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

 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!  :)

 

Hi Renee,

 

On my own sheet, it works just fine as the below screenshot.

overview1.png

 

So, please check each of the following:

  • Copy the formula as it is into the cell H3, and then fill it down using the Fill Handle which is this: Fill handle
  • Make sure that the calculation option is set to Automatic as follows:

Calculation Options.png

 

  • Make sure that the unused cells under cell D9 are completely blank because any non-numeric data will affect the result

It does work!  This is really great.  I have to take it apart and understand how this works.  I really appreciate it!

 

One last thing -  you mentioned that anything after D9 has to be blank.

But this is meant to be a running spreadsheet.  What do we do? 

 

Can we have an option where it will just keep going? Every time a multiple of 7 is reached in Column D, it places another X in column H... ?

 

Thank you!

Hi,

 

Not exactly anything after D9 has to be blank, it just the non-numeric data.

The formula already will just keep going so that every time a multiple of 7 is reached in Column D, it places another X in column H, but this in case if the new value in column D is within the range specified in the formula.

 

For example, in the formula I suggested the range is D3:D10, so any value within this range, the formula will take it into account.

You can expand the range as you want.