Breaking ties in excel ranking

Hi Guys

First up I'm not skilled at all on excel as we usually have a MI Team whom we send any and all raw data to. These guys sit enclosed never seen just heard via email when they magically convert your data and send you exactly what you want.

I started at a new company and they have NOTHING

Nevertheless I need help on ties where the subsequent numbers are being skipped due to the tie positions. i know no formulas.

Thanks

Jennifer

Hi Jennifer and welcome to the forum

Your description is a little short on specifics, but attached is a small example of ranking.
Random numbers in column A, a 'normal' rank in column B and a tie broken rank in column C for comparison.

Let me know if that's any use to you and if you need it explaining bit by bit.

If you're having trouble transferring that to your data then perhaps you could attach a small (desensitized) sample workbook and we can go from there.

BSB

Excellent thanks BSB. Your countif assisted me and it worked.

I was wondering if I could bother you with another issue. Calls are scored on a daily basis using a call evaluation sheet.
I looked at it and see that the N/A affects the overall score either inflating or deflating it.
Is there any way to leave the ability to choose N/A without it affecting the score.
Currently the assessors simply choose "yes" to solve the problem if it is N/A.
I'm not comfortable with this at all as it isn't a true reflection of department performance.

Thank you

Jennifer

Hi Jennifer,

In which cells are these averages causing an issue? I can only fine one =AVERAGE() formula (in cell A7) which in itself confuses me.
There are some formulas that are performing an average type function (cells D9 & G9 for example).

Without knowing what exactly you're trying to accomplish and what the expected results should be it's difficult to provide a solution.
However, perhaps =AVERAGEIFS() could be a help to you here.

Let us know a little more info on the intended outcome and we'll go from there.

BSB

Hi there,

I'm going to answer your questions one at a time LOL:

In which cells are these averages causing an issue? Column D where they have the option to choose N/A because,
when this column adds up their weighted average in C7, that's when the problem starts because if N/A is chosen then it sees it as
the agent having done the incorrect thing and scores them down which isn't true so instead "yes" is being chosen to combat this
which then inflates the score ...... also untrue.

I can only fine one =AVERAGE() formula (in cell A7) which in itself confuses me. Welcome to my world, this confuses the heck out of me
given my limited expertise hahahaha!

Let us know a little more info on the intended outcome and we'll go from there.So basically I want the score to remain the same because
it's what I've been given however I want to have the ability for the calls to be scored using "yes, no, N/A" and the impact it must have
on overall score should be as it states so N/A should have no impact on overall score stands to reason.

Hope I'm being clear, thanks so much.

Jennifer

Have a look at the attached. I've changed the relevant (I assume) formulas in columns D & G.
By turning the SUM part of the formula into a SUMIF that ignores rows with N/A, it should do what I think you mean.

Let me know either way.

BSB

Hi there

So we managed to fix the N/A issue. The next issue being those lines I highlighted in salmon pink should cause an automatic fail, in other words a final score of 0% due to it being a high risk issue.

Are you able to assist with this - I've attached the call evaluation sheet.

Thanks

Hi, I see no attachment.

BSB

OK thanks.

Not 100% sure I understand your need here, but I think this could be something like what you mean.

Try this in E10:

Formula:

Please Login or Register  to view this content.

Then if E13 or E14 show "No" then the % in E10 will be zero, otherwise it will continue as it was before.

Is that anything like what you need?

BSB