Change result #VALUE! to "message"
 

My formula returns last value in column of numbers. If all cells in range are blank, the formula returns #VALUE!. I would like formula to return message "No Scores". I have tried IF formula but keep getting message re circular reference
Can following formula be modified?

=INDEX(B35:B49,COUNTA(B35:B49),1

Thank
Bob Leonar

Change result #VALUE! to "message"
 

=IF(COUNTA(B35:B49)=0,"No Scores",INDEX(B35:B49,COUNTA(B35:B49),1))

rleonard wrote:

My formula returns last value in column of numbers. If all cells in range are blank, the formula returns #VALUE!. I would like formula to return message "No Scores". I have tried IF formula but keep getting message re circular reference.
Can following formula be modified??

=INDEX(B35:B49,COUNTA(B35:B49),1)

Thanks
Bob Leonard

Change result #VALUE! to "message"
 

One way:

=IF(COUNTA(B35:B49),INDEX(B35:B49, COUNTA(B35:B49),1),"")

In article ,
"rleonard" wrote:

My formula returns last value in column of numbers. If all cells in range are
blank, the formula returns #VALUE!. I would like formula to return message
"No Scores". I have tried IF formula but keep getting message re circular
reference.
Can following formula be modified??

=INDEX(B35:B49,COUNTA(B35:B49),1)