I have 5 cells and I want a 6th cell to be populated by the latest cell with a value in greater than 0
e,g
So I want K3 to check J3 first and work its way backwards until it finds a cell with a value greater than 0
Example 1
A2=3
D3=0
F3=1
H3=4
J3=5
So K3=5
Example 2
A2=3
D3=0
F3=1
H3=0
J3=0
So K3 =1
I hope I have made sense. Any help would be great. I tried googling it but it was difficult to describe.
It's awkward to determine "backwards" in a non-contiguous range, so how about:
=IF(J3>0,J3,IF(H3>0,H3,IF(F3>0,F3,IF(D3>0,D3,IF(A2>0,A2,"")))))
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
Try this,
=LOOKUP(2,1/CHOOSE({1,2,3,4,5},A2,D3,F3,H3,J3),CHOOSE({1,2,3,4,5},A2,D3,F3,H3,J3))
{1,2,3,4,5} is means 5 ranges (A2,D3,F3...) NOT any values.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks