Ask Your Question
0

The formula works except in one cell?

asked 2019-05-13 08:41:17 +0200

err504 gravatar image

updated 2019-05-13 09:12:09 +0200

I have a formula to audit some data which calculates correctly except in one cell.

I created a simplified spreadsheet as an example. The purpose is to compare the data on the $DailyLog sheet (Light blue columns on left) against the data on the $WeeklyPaystub sheet (Light yellow columns in center). The audit is done in the light gray columns on the right.

Column G21 should match F21, and H21 should say "OK".

image description

The formula for G21 is

=IF($D21="","",SUMPRODUCT(($A$2:$A$5000)=$D21,$B$2:$B$5000))

The formula for H21 is

=IF($D21="","",IF(SUM($F21-$G21)=0,"OK",SUM($F21-$G21)))

Everything else has been converted to number data.

image description

I wanted to upload an attachment of the simplified sample spreadsheet, but the site told me the file was too large. In actuality, the site claims the maximum file size is 1,048,576 kb aka 1,024 mb. My sample spreadsheet is only 19.8 mb.

Here's the attachment on a file sharing service. It will only be there 7 days before the service deletes it.

Why is cell G21 the only cell not adding up correctly?

edit retag flag offensive close merge delete

Comments

Why do you say it's not adding up correctly? your second screenshot shows 6 rows with "work week" 201852 - A2 to A7 (and two with "work week" 201952 - A8 and A9). The corresponding sum of values in B2:B7 is 44.28 + 31.32 + 57.24 + 28.62 = 161.46, which matches perfectly what you have in G2; and it mismatches F2 - correctly giving you the difference of 32.94.

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-13 08:58:09 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-13 09:23:46 +0200

err504 gravatar image

updated 2019-05-13 09:57:39 +0200

Apparently being overtired, I am also blind. My human error failed to notice that there were 2 years for Work Week 52: 2018, and 2019 as pointed out by @mike-kaganski.

The error is not where I thought it was, but rather in the formula for $DailyLog.$A which calculates the Work Week and apparently fudges the year if the Work Week straddles the end of one year and the start of another.

image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-13 08:41:17 +0200

Seen: 45 times

Last updated: May 13