Filter - "at least one condition" - wrong results

Hi,

I may be going mad, but I cannot get a filter with at least 1 of 2 conditions to work. Maybe my boolean is getting rusty...

I have 2 columns with monetary values. I only want to show rows where at least one of these is bigger than zero. Nothing complicated. The settings are attached in the screenshot below.

However I get the majority of the rows (there are only about 4 that match the condition I'm filtering for), mostly both being equal to zero. I've checked that rounding hasn't left a small amount of sub-pennies. I've changed the filter to "is greater than 1" to be on the safe side.

Any ideas?

Ta - Roland

SS Filter issue.PNG

Find more posts tagged with

Sales

Construction

Sheets

Comments

Mike Wilday

Hi Roland, make sure that the nearly invisible: Show rows that match option is set to "At least one condition" instead of "All conditions". If its set for all conditions both settings would have to match to produce the results you are looking for.

J. Craig Williams

Roland,

Based on your screenshot, the "at least one condition" appears correct.

The next thing I would check is any formulas that are being used to set 0 GBP. If the formula results is "0", that's text, not a number.

"Is greater than" won't work on text.

Without being able to see the data, I can't tell if your sums (first row) are correct. Even if there were "0"s, they still might be. The second row' percentages may also give a clue, but again, "0" and 0 may give the same result, so that won't be definitive either.

If you apply the filter, the first row with data should be displayed "Work Done 1 Actual GBP" is greater than 0.

Look for a row that you think should NOT be displayed and see what the values of the two columns are. Worst case, I create a temporary column and set the value to one of the two columns + 1 to see if it changes.

Hope that helps.

Craig

Mike Wilday

Oh my gosh. Just discovered that my broswer was hiding the images.

I had not seen the screenshot and was confused. When I posted my own I didn't see it either and submitted a ticket to support. hahaha. I am embarrassed.

Roland Horwood

Hi all,

thanks for the ideas. I've got a bit distracted by some other stuff, so I'll try to get back to this next week and see if I can work out the issue!

Thanks - Roland

Roland Horwood

Oops, my turn to be embarrassed...

After I looked at what I'd pasted I realised I'd selected the wrong second column in the criteria. My fault for having too many columns with similar names.

All working as expected now. Sorry for wasting your time!

Regards - Roland

J. Craig Williams

No worries, happens to the best of us.

Craig