Excel Conundrum

[jcollings]

Hi All

 

Here is a little excel problem I have just been set.

 

1 2 3 4 5

2 4 3 4

4 1 6 2

5 0 1

2 6 1

1 7 1

 

 

5 columns - I want to subtract the first value in the sequence of columns from the last. The data will be in random places in the columns 1-5 so I may be doing col 1 minus col 4 or col2 - col4 or col 3 minus col4 so I can't just do A1-A4 etc.

 

In my head I think there should be a way of doing it but I am struggling in reality! Any help gratefully received. Thanks

[Michael]

I can't really see an easy way around this, as Excel applies formulas to the same columns and rows. If the data could be organised consistently, then it should be straight forward.

 

From my perspective I'm thinking why you'd have 5, 4 or 3 values in some rows and not others.

[pubgrub277]

Would something like this work?

 

=A1-(INDEX(A1:A5,LOOKUP(2,1/(A1:A5<>""),ROW(A1:A5)),0))

 

index used to return a value found in a range lookup within that to find the last row reference containing a non blank value.

[pubgrub277]

sorry, reread the post and you don't know where the first value is.

 

=INDEX(A1:A5,MATCH(TRUE,A1:A5<>0,0))-INDEX(A1:A5,MATCH(TRUE,A1:A5<>0,1))

 

This would use the first index formula to find and return the first value in the range.

The second finds the last in the range.

 

The minus in the middle takes one value from the other.

[jcollings]

Thanks all. Sorted it now.

@pubgrub277 - yes, that is along the correct lines. I ended up with this to find first:=INDEX(A2:E2,MATCH(TRUE,INDEX((A2:E2<>0),0),0)) and this to find last =LOOKUP(2,1/(A2:E2<>""),A2:E2) - once I had those it was simple to take one from the other.

@Michael - they are a series of results and not everyone has a result for every test, hence the gaps.