I am creating a spreadsheet for my work. It is setup as a sales league table. An example is the are 7 teams. Cells G13 to G19 are used for sales, so each sale equals 5 points. What i am trying to do is find the MAX value of the cell range above and return a value of 8 (points) in another cell which will be added to that teams overall total. I have created a formula which returns the cell adress of the the cell that contains the max value and then used the IF function in another cell to allocate the 8 points.

eg.

Cell T20 contains the formula: CELL("address",OFFSET(G13,MATCH(MAX(G13:G19),G13:G19,0)-1,0))

and

Cell T13 to T19 contains the formula: =IF(T20="$G$13","8","0") (so if the above formula returns the cell address of G13 then 8 points are added to the cell T13 and added to the overall teams points.

The above might not be the best way as im very rusty with excel, but it does work!!! UNTIL two teams have the same number of sales, so say G13 contains 5 sales (which is the highest value in the range) but G14 also has 5 sales. The 1st formula only returns the 1st cell with the MAX value and ignores and cells which have the same value. So it returns the cell address of $G$13 and allocates the 8 points but nothing happens the points for T14.