Notification at multiples minus a number

John Fasano

Hello,

I have a spreadsheet where each row counts hours, then a column keeps a running total of all hours.

I would like to be notified at certain intervals as every 150 hours I need to do a task however I need some prep time so I would like to be notified 20 hours before 150.

So when my column value reaches 150, 300, 450, 600 I would like to be notified at 130, 280, 430, & 580.

While 130 is 150-20, I cant track factors of 130 since then I'll get notified 260, 390, & 520.

Any suggestions? thank you!

Paul Newcome

Try a MOD function.

IF(MOD(SUM(Hours:Hours), 150) = 130, 1)

I am not sure of your exact setup, but MOD150 would only present the numbers 0 - 149. So whenever MOD150 equals 130, you are now 20 hours before the next multiple of 150.

John Fasano

Hi Paul,

That as very helpful, thank you!

Paul Newcome

Happy to help. 👍️