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, theyre 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.

The problem is horses aren't running every day, so finding the returns for the last start to the 10th last isn't returning a value because the vLookups I'm using are returning a "#N/A" value if the horse didn't race that day.

The formula I'm using to find the last result is: =IFERROR(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: =IFNA(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.

 

[hernantorres23]

Hi, please try prepare a data example using add-in specifically for this and it can be found here XL2BB

 

[AustSportingPix]

Bumping this with a little more info:


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:
Code:
=(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

 

[Fluff]

I've asked on a few places,

In that case please supply links to the other sites where you have asked this question, as per Rule#13
Thanks

 

[Fluff]

Are you trying to get the header row value for the last column with data per row?

 

[AustSportingPix]

Mainly reddit:

https://www.reddit.com/r/excel/comments/jplxbi

I've asked on facebook among my friends.

 

[AustSportingPix]

Yes.

So the data/value of horse A's last run which could be on the 5th, horse b's last run which could have been on the 1st.

hopefully I'm explaining it right

 

[Fluff]

Maybe in D2 filled down

=LOOKUP(2,1/(Q2:BK2<>""),$Q$1:$BK$1)

 

[AustSportingPix]

Maybe in D2 filled down

=LOOKUP(2,1/(Q2:BK2<>""),$Q$1:$BK$1)

THANK YOU THAT WORKED

For about 2 weeks I've been trying to get it working, I changed the way they were being used because I was getting so many double ups, this has fixed it.

 

[Fluff]

You're welcome & thanks for the feedback.