Formula help

[jennyL]

Hi,Some help please. I've looked at LOOKUPs, IF, ANDs and I can't seem to figure out the correct formula to use.

Column A contains a half year score and Column B contains a full year score, Column C is the 'reference' for the final year score.
I'm looking for a formula/way for excel to return the Final Result.
Hope this makes sense
[TABLE="width: 500"]
<tbody>[TR]
[TD]HY Score
[/TD]
[TD]FY Score[/TD]
[TD]Final result
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]
There are 15 rows in total.
Thanks in advance for your help

 

[Michael M]

Ok...how do you arrive at the figures in column "C" ?
Is it a ROUNDED average ?

 

[jennyL]

Hi Michael,

It's not rounded, there is no mathematical formula to work out the Final Result.

 

[steve the fish]

I believe he is asking if you require a formula to complete column C what rules are you using to come up with the result in column C? Take the first line. If HY score = 1 and FY score = 1 why is column C equal to 1? Same for the 2nd line with 1, 2 then the result 2? To create a formula it is necessary to understand the rules.

 

[jennyL]

Thanks Steve, It's actually for a performance rating and there isn't a formula for column C. To help initially explain I changed my 'words' into numbers. Then when I get help with the formula I can change the numbers back to how I need them to be. So hence, no maths involved as there is a little judgement used.

 

[Michael M]

I'm waaay confused now ??
You will need to give us some sort of criteria / logic to arrive at the numbers you have in the sample ???

 

[jennyL]

The below table is actually what I'm working with, it's actually a matrix however I've converted it into a table to try and get Excel to do the work for me.. I have the values for column A and B and would like Excel to return the value of column C:

[TABLE="width: 388"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]KPI[/TD]
[TD]Behaviour[/TD]
[TD]Final[/TD]
[/TR]
[TR]
[TD]1. Missed[/TD]
[TD]1. Unacceptable[/TD]
[TD]1. Unaccetable[/TD]
[/TR]
[TR]
[TD]1. Missed[/TD]
[TD]2. Acceptable[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]1. Missed[/TD]
[TD]3. Exceptional[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]2. Below[/TD]
[TD]1. Unacceptable[/TD]
[TD]1. Unaccetable[/TD]
[/TR]
[TR]
[TD]2. Below[/TD]
[TD]2. Acceptable[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]2. Below[/TD]
[TD]3. Exceptional[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]3. On Target[/TD]
[TD]1. Unacceptable[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]3. On Target[/TD]
[TD]2. Acceptable[/TD]
[TD]3. Successful[/TD]
[/TR]
[TR]
[TD]3. On Target[/TD]
[TD]3. Exceptional[/TD]
[TD]4. Exceptional[/TD]
[/TR]
[TR]
[TD]4. Exceeds[/TD]
[TD]1. Unacceptable[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]4. Exceeds[/TD]
[TD]2. Acceptable[/TD]
[TD]3. Successful[/TD]
[/TR]
[TR]
[TD]4. Exceeds[/TD]
[TD]3. Exceptional[/TD]
[TD]4. Exceptional[/TD]
[/TR]
[TR]
[TD]5. Exceptional[/TD]
[TD]1. Unacceptable[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]5. Exceptional[/TD]
[TD]2. Acceptable[/TD]
[TD]4. Exceptional[/TD]
[/TR]
[TR]
[TD]5. Exceptional[/TD]
[TD]3. Exceptional[/TD]
[TD]4. Exceptional[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

It is absolutely impossible to do what you have asked without rules.

 

[Michael M]

Well, not quite impossible, but you would need to setup a table, with ALL possible combinations of the columns "A" & "B"...AND the possible results.
And unless you have thousands of rows to work through, It wouldn't be worth the effort.
As well as the comment by Steve !!!....we would need ALL of the rules / criteria to even start to think about it !!

I'd like to see the matrix though, it might help in creating something like an INDEX / MATCH function, and it might be easier than a table

 

[jennyL]

Thanks I appreciate you both looking at it.

[TABLE="width: 668"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Overall KPI Rating[/TD]
[TD]5. Exceptional[/TD]
[TD]2. Low Performer[/TD]
[TD]4. Exceptional[/TD]
[TD]4. Exceptional[/TD]
[/TR]
[TR]
[TD]4. Exceeds[/TD]
[TD]2. Low Performer[/TD]
[TD]3. Successful[/TD]
[TD]4. Exceptional[/TD]
[/TR]
[TR]
[TD]3. On target[/TD]
[TD]2. Low Performer[/TD]
[TD]3. Successful[/TD]
[TD]3. Successful[/TD]
[/TR]
[TR]
[TD]2. Below[/TD]
[TD]1. Unacceptable[/TD]
[TD]2. Low Performer[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD]1. Missed[/TD]
[TD]1. Unacceptable[/TD]
[TD]1. Unacceptable[/TD]
[TD]2. Low Performer[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1. Unacceptable[/TD]
[TD]2. Acceptable[/TD]
[TD]3. Exceptional[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Overall Behaviour Rating
[/TD]
[/TR]
</tbody>[/TABLE]