Nearest guess score - both teams

oftwominds

New Member
Joined
Feb 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to determine the closest guess score for a match result. I have used the index match min array formula to determine the closest guess number for team A and Team B and then conditional formatting to highlight the cell. Eyeballing I can see that name5 is the closest to guessing both team score results (boxed cells). How do I modify the formula to highlight thIs row with the guess closest to both teams' actual score rather than each team separately?

=INDEX(B7:B26,MATCH(MIN(ABS(B7:B26-B4)),ABS(B7:B26-B4),0)) outputs the nearest score number

=INDEX(A7:A24,MATCH(MIN(ABS(B7:B24-B4)),ABS(B7:B24-B4),0)) outputs the name of closest guess

1644842076579.png
 

Attachments

  • 1644841720317.png
    1644841720317.png
    41.7 KB · Views: 11

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about
+Fluff 1.xlsm
BC
43523
5
6
71014
82024
91727
101826
112723
121517
132327
142535
152413
161727
172320
181517
192632
201617
212832
22
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:C26Expression=(ABS($B7-$B$4)+ABS($C7-$C$4))=MIN(ABS($B$7:$B$26-$B$4)+ABS($C$7:$C$26-$C$4))textNO
 
Upvote 0
Solution
How about
+Fluff 1.xlsm
BC
43523
5
6
71014
82024
91727
101826
112723
121517
132327
142535
152413
161727
172320
181517
192632
201617
212832
22
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:C26Expression=(ABS($B7-$B$4)+ABS($C7-$C$4))=MIN(ABS($B$7:$B$26-$B$4)+ABS($C$7:$C$26-$C$4))textNO
Perfect, Many thanks.

Trying to modify to also highlight the name for the closest guess i.e Winner.???
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top