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

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]

Want to read more?

Check out the full article on the original site

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