Return last number in a Row Range

[Tonyd789]

Hi All

i have a basic table with months across the top and topics down the right side, each 1 of those topics scores a point each month. i'm after a formula that will return the last score for a topic in each row.

Can someone please help me.

Regards

 

[Scott T]

adjust cells to fit your data and copy down. The lookup value, 99999999999 in this example, should be larger then any score you will enter.

=LOOKUP(99999999999,B2:M2)

 

[Tonyd789]

Simple as that, thank you very much.

 

[Tonyd789]

Additional, How can i get the second to last result ie the previous score.

 

[Scott T]

Try. once again the 99999999999 needs to be larger then any score you would enter.

=INDEX(B2:M2,MATCH(99999999999,B2:M2,1)-1)

 

[Tonyd789]

Returns a value of 0, September scored 9 Aug scored 8, should be returning the august score.

This is my formula =INDEX(D9:AC9,MATCH(101,D9:AC9,1)-1)

 

[Scott T]

Does every column have a value?

is your data
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]Mar
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]8
[/TD]
[TD="width: 64, bgcolor: transparent"]5
[/TD]
[TD="width: 64, bgcolor: transparent"]7
[/TD]
[/TR]
</tbody>[/TABLE]


Or is it
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Jan[/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can you post a sample of your data

 

[Tonyd789]

Its very much like your first example, however there may be certain months that may have no score.

 

[Scott T]

With out seeing sample data I do not know why you are getting zero, you should only get zero if the second to last is blank or zero. To account for the possibility that the second to last will be blank and return the last score this should work. If there is no second to last, only one score entered, then the formula will return blank.


This is an array formula and must be entered with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IFERROR(INDEX(D9:AC9,LARGE(IF(D9:AC9<>"",COLUMN(D9:AC9)-COLUMN(D9)+1),2)),"")

 

[Tonyd789]

Thank you very much for your help on this, this is exactly what i wanted.