3

I have a column of ascending numbers. I want to mark every cell in this column that is larger or equal to an integer multiple of a predetermined value, e.g. 300 in the example image.

I tried using the MOD function but it did not allow me to solve this task. The mod function generates a ziz-zag waveform (graphically speaking) and I understand that in the general case I need to find the local minimum to select the right cell, but I am not quite sure how to do that (the numbers in my data aren't necessarily integers, so MOD(x, 300) is almost never equal to zero).

enter image description here

(The values actually will be the running hours of a motor and some fixed value is the scheduled maintenance work on it)

4
  • 2
    You were heading in the right direction with MOD, but you needed integer division instead using =INT(B1/300) or =QUOTIENT(B1,300). Now to flag only the first number which is larger than n*300 as in your example, =QUOTIENT(B2,300)<>QUOTIENT(B1,300) (starting in cell C2). You can also use this as a custom conditional formatting formula (remember to use relative refs "B2" not absolute "$B$2"). Commented May 3, 2021 at 14:32
  • Grateful for your precious comment. My question is blocked for "the lack of clarity"(??), and I didn't have time to clarify it. So, your advice is so unexpected! Have never heard of QUOTIENT function. Looks like I am going to succeed!
    – off-signer
    Commented May 4, 2021 at 0:15
  • You should still be able to edit your closed post (within 5? days), after which it will be submitted to the reopen queue for review. I think the issue was that readers had to look to the image to try and understand what it was that you were asking. The text wasn't all that clear. Appropriate text is important as that is the way that google matches other people's queries with similar problems to yours, making this question worth answering "for the sake of the many". Commented May 5, 2021 at 8:30
  • Edited to my best.
    – off-signer
    Commented May 6, 2021 at 14:48

1 Answer 1

1

The @Mobus comment was an answer. I should use the conditional formatting with this simple formula:

=INT(B2,300)<>INT(B1,300)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .