Forum Discussion
need help with a formula to place an X in a cell when values in another column reach multiples of 7
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","")
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.
So, please check each of the following:
- Copy the formula as it is into the cell H3, and then fill it down using the https://support.office.com/en-us/article/display-or-hide-the-fill-handle-80918200-9ae9-4615-93c9-13d4f1496f81 which is this:
- Make sure that the calculation option is set to Automatic as follows:
- 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!
- Copy the formula as it is into the cell H3, and then fill it down using the https://support.office.com/en-us/article/display-or-hide-the-fill-handle-80918200-9ae9-4615-93c9-13d4f1496f81 which is this:
