ExcelBanter - View Single Post

wrote...
....
Row 1 is problem 1, Row 2 is problem 2, and so on. Lets say in column
A is my alpha column. In cell A1 I have the word "Sell". Column B is
my numeric column. In cell B1 I have the value "20".

In a worksheet two, A1 reads "Buy" and B1 reads "10".
In worksheet three, A1 reads "Sell" and B1 reads "22".
In worksheet four, A1 reads "Sell" and B1 reads "10".

Because worksheet four gave the correct alpha value AND had the closest
numeric value (without going over) it should be provided a point for
providing the correct answer for problem 1. This means the value for
cell F1 in the master worksheet should reflec this and read "1".
Alright, does anyone have any idea?

Presumably the target sell price is the highest price obtainable, so
setting a sell price above it would result in never selling, which
would be a good reason to tossing sell prices above the target. If the
answer were Buy, on the other hand, presumably the price would never go
lower than than the target buy price, so any answers with buy prices
below the target would also never be realized, so when the correct
answer is Buy, you'd only want to consider answers with prices
*greater* than or equal to the target buy price.

Presumably if multiple other worksheets have the same best answer, both
worksheets would get a point.

You'll need a list of all the answer worksheet names in some range or
array. I'll assume its named AWSLst.

If all worksheets have alpha an numeric answers also in columns A and
B, respectively, then in the master workbook,

D1:
=AND(Two!$A1="Sell",Two!$B1=MAX(IF(N(INDIRECT("'"& AWSLst&"'!"
&CELL("Address",$B1)))<=$B1,N(INDIRECT("'"&AWSLst& "'!"
&CELL("Address",$B1))))))+(Two!$A1="Buy",Two!$B1=M IN(IF(N(INDIRECT("'"&
AWSLst&&"'!"&CELL("Address",$B1)))=$B1,N(INDIRECT ("'"&AWSLst&"'!"
&CELL("Address",$B1))))))

Fill D1 right as needed and replace Two with Three in E1, Two with Four
in F1, Two with Five in G1, etc.