# How can I find cells in Excel whose values are not in increments of 0.25?

I have a spreadsheet containing timekeeping data - hours worked, vacation leave, sick leave, etc.

Our policy is that all entries must be in increments of 0.25. Due to a technical glitch, we had to 'turn off' a setting that ensures entries are in 0.25 increments. Now, I need to find out if any of the entries are not in 0.25 increments.

Can anybody think of a formula that can search a row of data (for each employee on the spreadsheet) that will determine if any of the pay code entries within the row were not an increment of 0.25?

For example

• one row might contain 74.5 hrs worked and 5.5 vacation hours; this is okay because both values are in increments of 0.25.
• However, if someone had 74.5 hours worked and 4.87 hours of vacation, I need to find the 4.87 usage because it's not in an increment of 0.25.
• =mod(A1,.25) - anything not 0 is off – Raystafarian Jan 4 '16 at 13:53
• Thank you Raystafarian! I summed the row and then applied the 'mod' formula you provided. Out of 2,170 rows, I only had 3 rows where employees did not use increments of 0.25 for their timecard entries. And, this is what I needed to zero in on. Thanks!! -Annette – Annette Jan 4 '16 at 14:01
• @Raystafarian, could be worth moving to an answer. If he does Annette, please do mark it as answered – MyDaftQuestions Jan 4 '16 at 14:25

You can use the mod function to return the remainder of the sum of the rows, identifying rows you need to check.

=Mod(A10,.25)

If you have more than 1 type of time reported per line, you can use

{=SUM(MOD(B3:D3,0.25))}


to identify which line has an improper entry. (you must press CTRL+Shift+Enter after typing the formula to create the array formula)

This formula assume you have: EmployeeID in Col A, Regular Hours in Col B, Overtime Hours in Col C, and Paid Time Off in Col D.

If you have more columns, expand the range.

edit: Formula changed per Máté's suggestion.

• Does it make sense to sum within mod? what about {=SUM(MOD(B3:D3,0.25))} – Máté Juhász Jan 4 '16 at 14:55
• Doesn't (A + B + C) / .25 equal A/.25 + B/.25 + C/.25? Either formula will work. – B540Glenn Jan 4 '16 at 19:48
• Mod is different than simple division, please check: (0.1+0.15) mod 0.25 vs 0.1 mod 0.25 + 0.15 mod 0.25 ! – Máté Juhász Jan 4 '16 at 19:51
• Máté, I see your point. Make it an answer and I'll vote for it. – B540Glenn Jan 5 '16 at 16:51
• It's your answer:), just correct it, my purpose was to help improving it. – Máté Juhász Jan 5 '16 at 18:15