Sep 04 2022 01:42 AM
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?
Sep 04 2022 02:48 AM
@Lukereeves Would be helpful if you could share the formula.
Sep 04 2022 03:00 AM
Oops
=FILTER('PB 3 WORKINGS'!B3:E16000,'PB 3 WORKINGS'!E3:E16000<0)
Column E3 is a sum of 2 other columns.
Sep 04 2022 03:16 AM
@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.
Sep 04 2022 03:42 AM
@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.
Sep 04 2022 03:47 AM
Sep 04 2022 03:57 AM
Sep 04 2022 04:14 AM