Asked this the other day & didn't get a reply, Trying to find the last number in a row, but getting errors because of #N/A returns.

[AustSportingPix]

So my horse racing ratings are coming along, they're finding winners and value runners.

My database table is a large file. In it, I want to find the peak return, average return (they're both done), plus the returns for each of the last 10 returns for each horse.

The table will have a column for each date from 2020/09/23 to as far as it will take me.

One of the main problems I'm getting is because here they have races every day and the horses have spaced runs finding I'm having trouble with blank cells, most are 7 to 14 days apart, some will be months.

The other thing is they're benchmarked ratings, so some horses will return a result above zero, some below. So even filtering out those just above zero won't work.

The formula I'm using to find the last result is:

=(INDEX('All Horse Ratings.xlsx'!Allhorses[@[20200923]:[20201106]],1,COUNTA($Q2:$BI2))

(I change this in a few cells to get the last 10 results)

The formula I'm using to get the ratings into my database is:

=(VLOOKUP($A2,INDIRECT("[ratings.xlsx]"&Allhorses[[#Headers],[20200923]]&"!$A:$C"),2,0)

What do I do to because I'm only getting errors or false returns.

Its also a very automatic process. The ratings are worked out in 1 workbook, the results are copied & pasted into a workbook called "ratings" and the database access' those using a mix of vlookup & indirect formulas.
I was just simply copying & pasting into the database, but I was spending about half an hour a day finding all duplicates, writing in the rating in the first free cell, deleting the duplicate and moving on to the next one.
At least with the vlookup all I have to do if highlight the ones already there and mass delete them.

On race day all runners should be returned into the form guide workbook I'm using which is working well.

I've asked on a few places, I've been told pivot tables (that just won't work) been told filter formulas (I've tried and still get a blank or an error).


Has anyone got a way to fix this?

I've uploaded a sample of the spreadsheet

sample.xlsx

Microsoft Excel Workbook

1drv.ms

Thanks

 

[Fluff]

Duplicate Trying to find the last number in a row, but getting errors because of #N/A returns.

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).