CODEWITHSUNDEEP
excelif-statement
user85352
user85352

Reputation: 29

Microsoft Excel - Grabbing values > 0

Is there any way using Microsoft excel to do an equation that does something like this:

IF(Condition, If true, If false) <-- Just in case, this is how an if statement is formatted

IF(a cell in a range has a value other than zero, put that/those value(s) here, do nothing)

For example:

enter image description here

IF(B1:B13 is something other than 0, grab that value, do nothing)

Based on the table above and the "equation" I am trying to figure out, it would return 12 and 17, 15.

Let me know if I need to clarify anything.

Upvotes: 0

Views: 59

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

If your version of Excel supportsTEXTJOIN(),use:

=TEXTJOIN(",",TRUE,IF(B1:B13=0,"",B1:B13))

enter image description here

EDIT#1:

Some versions of Excel may require this formula to be array-entered.Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

If one has the Dynamic Array formula FILTER():

=FILTER(B2:B100,B2:B100<>0)

enter image description here

If not then put this in the first cell and copy down:

=IFERROR(INDEX(B:B,AGGREGATE(15,7,ROW($B$2:$B$100)/($B$2:$B$100<>0),ROW($ZZ1))),"")

enter image description here

Upvotes: 1

Related Questions