You are here:

# Excel/Formula with 3 possible answers

Question

Performance sheet
Hi I am currently working on a performance sheet for my store I have split the staff into 3 teams and all the KPI's for the staff are recorded and an average for the team is produced.

I want to give points based on the 3 teams average scores. (3 points for a 1st place, 2 points for 2nd place, 1 point for 3rd place) (see - blue cells)

I have the values ready (see - conditionally formatted cells) I just dont know how to do the formula. I have tried several nested IF statements but they aren't producing the right answers. (see below for my pathetic attempt)

=IF(B9>B16&B23,"3",IF(B16>B9>B23,"2",IF B23>B9>B16,"2",IF B9<B16&B23,"1","3"))))

I understand that access would probably be a lot easier for this but excel is the only we are able to use.

Hi Ben,

Assuming you are going to place the formula in E9, E16, and E23, the below formulae should give you the required results:

Formula in E9 =IF(AND(B9>B16,B9>B23),3,(IF(OR(B9>B16,B9>B23),2,1)))

Formula in E16 =IF(AND(B16>B9,B16>B23),3,(IF(OR(B16>B9,B16>B23),2,1)))

Formula in E23 = =IF(AND(B23>B9,B23>B16),3,(IF(OR(B23>B9,B23>B16),2,1)))

I would like to highlight that your current approach does not cover the scenario where there's a tie between two or more teams.

Hope this helps,

Gulshan.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### GULSHAN PURSWANI

##### Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

##### Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India