AverageIF function returns with a #DIV/0! because the criteria does not exactly match.
Our take
I am working with a set of values where there are repeated x values with multiple y values. For example, x value of 10 would have values like -0.5, -0.51, -0.52. I am trying to get the average of these y values for each x value in 0.01 increments.
The averageif work functions works up until a certain point that it just returns with #DIV/0!. When I check the error, the criteria doesn't match the cell on its left. In the photo, the criteria shows as 16.51000....01 when the real criteria (on the left) is 16.51. Where is the error coming from?
The way I typed up the x values in 0.01 increments is typing 0.0 and 0.01 and dragging that down to my target value. I tried the sequence function as well but I get a similar error.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Average cells in a column and ignore those with 0 and #VALUE, but only if I different column contains a certain year.Back again because everyone was so helpful! I'm trying to Average cells in row AW, while ignoring Os and errors, while also only doing that row if column AL contains a certain year. I think that makes sense? I know it needs to be an AVERAGEIF formula, but when I do "*2026*" as the Criteria, before even attempting the >0, the formula breaks. submitted by /u/Wicked-Storm [link] [comments]
- Blank cells are being summed as a value greater than five using a SUM(IF functionHi! 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]