1

I have two columns of data, one with names (A7:A), and one with percentages (B7:B).

I am trying to use the filter function to show me the names of results with non-zero percentages. I also want to change what it spits out to have only cells in column B be multiplied by a value in a specific cell (B2) but keep column A the same.

Things tried:

=filter(A7:B,B7:B>0)

This works to filter out zero results but doesn't change column B

=FILTER($A$7:B*B2,N(B7:B)>0)

This changes column B to the desired value, but column A has #VALUE! error since it's a word and can't be multiplied by a number

=filter(A7:B,(B7:B>0)*(B7:B*B2))

This gives the right name values but keeps the original percentage values and doesn't change it.

=FILTER(A7:B*B2,B7:B>0)

This gives the right number values but gives #VALUE! error for name as it is not a number.

enter image description here

1 Answer 1

0

Two methods come to mind:

Array

={FILTER($A$7:A,B7:B>0),FILTER($B$7:B*B2,B7:B>0)}

Query

=QUERY(A6:B,"SELECT A, B*" & B2  & " WHERE B > 0 AND A IS NOT NULL LABEL A 'Recipe', B* "& B2 &" 'lbs'", 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.