Forum Discussion
FILTER function
Hi,
I have currently migrated from Googlesheets to EXCEL.
I have an issue with the FILTER function, I have a simple formula that looks back at another sheet and returns any values less than zero.
The values I am looking at are a SUM of two other values (formula in the cell), it seems the FILTER function does not return a value due to a formula in the cell its looking at.
Is there a way of getting this to work please?
Lukereeves Function structure for FILTER() is different in google sheet and excel. So, share few sample data as well as your faulty formula so that we can correct it.
- Ok, it works fine with a clean sheet, no issues.
Just used this formula on the same sheet and a different sheet all good.
=FILTER(B3:D22,D3:D22<11)
On my sheet I have several sheets copied from other workbooks, I pull the data required into a new sheet called workings, I then want to filter this to only show me values less than 0.
I just started started again, and cleaned up things, I then used this formula (same as the one above) =FILTER(Workings!A7:P15250,Workings!P7:P15250<0)
It always returns #NA
Formatting issue?
Can i post the spreadsheet?Lukereeves Then the underlying data contains #N/A.
Lukereeves Would be helpful if you could share the formula.
Oops
=FILTER('PB 3 WORKINGS'!B3:E16000,'PB 3 WORKINGS'!E3:E16000<0)
Column E3 is a sum of 2 other columns.
Lukereeves The formula syntax seems to be correct and FILTER looks at the values of the cells to be included, not the formulas in those cells.
You say the function "does not return a value". But you don't specify the result that is being returned. It can't be just nothing. Perhaps a #CALC! error, meaning no matching records were found. If so, you may add a third optional argument specifying what needs to be returned if no matches are found.
Or do you get a dialogue box stating "There's a problem with this formula"? Perhaps you need you use a semi-colon rather than a comma to separate arguments in your local Excel set-up.
