Excel/Formula with 3 possible answers
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.
Please can you help me.
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,
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