Select and Return a text based on numerical differences

Hi, a comment on the attached 2003 Excel Worksheet explains what I need, I have left my lookup formulas in case Im nearly there. I have T&S Excel 2003, thanks.

You need to perform a football style ranking score - count of wins + some differential as a fraction, along the lines of this in AE2, copied down:

=IFERROR(W2+(Y2-Z2)/100,0)


Then use this in C3 to get your MOTM:

=INDEX(U:U,MATCH(MAX(AE:AE),AE:AE,FALSE))

My bad - I zoned out on your version being 2003.

Use this instead:

=IF(ISERROR(W2+(Y2-Z2)/100),0,W2+(Y2-Z2)/100)

Point differentials are the tie-breaker, which was the only measure I could see that could be used that way, and that is why I divided by 100, so that someone who won 2 games would get a score between 2 and 3, but not over 3.

No problem - I'm glad you are happy with the result.

It is possible that there could be tie based on that formula - you will get the first name in that case. You can add another level of tie-breaking by using some other less-important numerical measure, divided by 10000 instead of just 100. The formula I use for my football table is

=I4+H4/100+F4/10000-ROW()/1000000000

where I4 is points (3 for a win, 1 for a tie), H4 is goal difference, F4 is goals scored, and the last is used to make sure that there are no ties (which would mess up my data extraction formulas). If teams were truly tied at that level, then a completely different tie-breaker is kicked in if it matters (like, who is the champion).

Maybe use words - I still have your original file.

In AF5, enter this formula

=OR(A5=$AF$1,C5=$AF$1)

and copy down for more rows than you have data. You can copy it for thousands of rows, if you like, to future-proof your sheet.

Then

1) Copy the code below.
2) Right-Click the sheet tab
3) Select "View Code"
4) Paste the code into the window that appears.

And if you have XL2016, you will need to do this:
5) Save the file as a macro-enabled .xlsm file.

Please Login or Register  to view this content.

When you enter a name into cell AF1, the sheet will filter to show only those rows with that name in A or C. If you delete the name in AF1, all the rows will show.

Try posting another copy of your workbook - click the "Go Advanced" button to the lower right of the editing window, then look for the "Manage Attachments" and use that instead of the 'Paperclip' version of attachments, which doesn't work for me.

I tried 3 different reply Edits but I can only edit what I wrote not add the file, can I email it to you, or open a new thread? There is no "Go Advanced" or "Manage Attachments", Is there an Admin I can talk to about this, Ill see if I can find a link to one.

You could also upload your file to a file-sharing site like onedrive.live.com and get a link to the file, and post the link here.

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

Also, Bernie, we need to use the file upload facilities on the site. Not all members are able - or willing - to access file-hosting sites

I uploaded the file, I hope it goes I see its uploaded. I changed my title, but it remains the same in replies. Why was I unable to even see uploading from my thread following what Bernie explained. Am I prevented by the Forum software?

Originally Posted by FDibbins

Also, Bernie, we need to use the file upload facilities on the site. Not all members are able - or willing - to access file-hosting sites

I wish we always could. The attachment button broke, for me, a while ago. I never could figure out why cannot choose a file to upload using the paper-clip icon. The advanced / manage attachments does work for me, but it does not seem to work for everybody. So when a poster claims to not be able to use it, or has a large file, the only option for sharing files is another site.

I get "The file is corrupt and cannot be opened" with the second file.