2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Blank cells are being summed as a value greater than five using a SUM(IF function

Our take

In this scenario, you're encountering unexpected results when summing breaks in a large dataset of 7-day schedules. The SUM(IF function is intended to calculate breaks based on workdays, yet some blank cells are being incorrectly counted as values greater than five, leading to inflated totals. This issue arises intermittently, despite all columns being formatted as numbers. While manually entering "0" resolves the problem, this approach is impractical for a large dataset. Let’s explore effective solutions to ensure accurate calculations without extensive manual adjustments.

Hi! I have a large dataset of 7-day schedules that I am summing to calculate breaks.

The function is setup like this:

=SUM(IF(CELL1>5, 0.5, 0)), (IF(CELL2>5, 0.5, 0)) and so on until all seven days are tabulated.

Because a full time person would only work 5 days a week, at least 2 days are blank per row, but we're open all 7 days and I want to sum the entire spreadsheet, so I have to count all 7 days. A full time person should have 2.5 hours per week.

For some reason the function is mostly working, but about a third of the results are larger than they should be because it's counting some blank cells as greater than 5. For example, a part time person working only 3 days should have 1.5 break hours, but the function returns 3.5 because it is counting all the blank cells as containing a value > 5.

What's really strange is it is only doing this some of the time. Every row has at least two blank cells, but only about a third of the sums are wrong. I can't figure out why.

The columns are all formatted as a number. The value does update correctly if I manually enter a "0" in the blank cells, but this is a very large dataset and that would take forever.

Thoughts?

submitted by /u/andylefunk
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article

Tagged with

#large dataset processing#real-time data collaboration#real-time collaboration#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#row zero#rows.com#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#Excel compatibility#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet#Excel alternatives