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.

Status
Not open for further replies.

AustSportingPix

New Member
Joined
Oct 5, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
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

Thanks
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top