CODEWITHSUNDEEP
excel-formula
JohnB
JohnB

Reputation: 4359

How do I get the first previous non-zero cell in column using an Excel Formula?

Consider the following spreadsheet snippet:

enter image description here

I'm trying to write the following forumla:

For a give row, for the same Sales#:
   If Type=1 OR Type=2, go the first non-zero previous cell in the column
   and copy the lineprice

I know how to examine the type field:

=IF((OR(VALUE(BA7)=1,VALUE(BA7)=2)), ?????, "")

I'm not sure how to retrieve the lineprice. I would like to do this via a formula if possible.

Thanks

Upvotes: 0

Views: 88

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

=IF(OR(C7={1,2}),INDEX($B$1:$B6,AGGREGATE(14,7,ROW($B$2:$B6)/(($A$2:$A$6=A7)*($B$2:$B6<>0)),1)),0)

enter image description here

Upvotes: 1

Related Questions