Help with IF function, small, exclude zero's, other

**MWORSHAM** · 12-29-2012, 12:01 AM

I'm trying to manage a lot of data for a call center and I'm building a rewards program that rewards 10 agents each week for having the lowest hold time. I have a column that i'm assigning a point value to that will give 1 point if they are one of the 10 lowest, 0 points if not. I have other columns doing similar things and then auto summing across to give a rewards total.

Here is the formula that I'm using...
=IF(OR(K5=(SMALL($K$5:$K$137,1)),K5=(SMALL($K$5:$K$137,2)),K5=(SMALL($K$5:$K$137,3)),K5=(SMALL($K$5:$K$137,4)),K5=(SMALL($K$5:$K$137,5)),K5=(SMALL($K$5:$K$137,6)),K5=(SMALL($K$5:$K$137,7)),K5=(SMALL($K$5:$K$137,8)),K5=(SMALL($K$5:$K$137,9)),K5=(SMALL($K$5:$K$137,10))),1,0)

And here is the problem I'm running into...
If someone took the week off, their average hold time for that week is 0. This formula is rewarding them and I need it to exclude zero's.
I've tried a few things but all has failed.

After this obstacle, I will need to figure out how to handle issues where 1 person is the lowest with a score of say 10 and then 15 people all have the second lowest with a score of 11. In this example I would need 16 people to be awarded instead of the first 10 since we have a tie. I have no idea how to do that though. Any help would be greatly appreciated. I'm running out of hair to pull out.. haha

**FDibbins** · 12-29-2012, 12:57 AM

MWORSHAM welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**bebo021999** · 12-29-2012, 01:28 AM

Try this:

Please Login or Register  to view this content.

**MWORSHAM** · 12-29-2012, 03:52 PM

Thank you for the warm welcome.

I wasnt able to upload a copy last night due to all of the security where I work however I've managed to create a small simplified version to show the problems I'm running into.

In this example, I want to reward the 5 agents with the lowest hold time as well as any agent who met th goal of 45 seconds or less. No one likes to be on hold

In column D the formula I'm using is doing everything it's supposed to, however it's rewarding Leah, Brandon and Antoine because their average hold time is zero but the problem is that they were on vacation and their average is 0 because they didn't take any calls. I need to figure out how to exclude them with a formula.

I need to do the same with agents who met the goal in column E and I can't find a way to make it work.

The actual spreadsheet is pretty much a database updated weekly with 7 worksheets of data, a home page to compare multiple stats by team, department and floor wide then it breaks everything down by week, month and agent. The actual page that I'm doing this with has about 130 agents, with probably 15 columns like this for rewards.

I've researched how to do this for over a week now and I've decided that it's time to ask the masters.

**daddylonglegs** · 12-29-2012, 04:03 PM

Try this formula in D3 copied down

=(B3>0)*(B3<=SMALL(B$3:B$15,COUNTIF(B$3:B$15,0)+5))

and this one in E3

=(B3>0)*(B3<45)

**FDibbins** · 12-29-2012, 04:13 PM

Based on your example, try these, copied down...
for the top 5
=IF(RANK(B3,$B$3:$B$15,0)>5,"",RANK(B3,$B$3:$B$15,0))
for Met Goal
=IF(AND(B3>0,B3<45),1,0)

Im thinking that the chances of more than 1 falling in the top 5 would not be impossible. how youle you want to handle that?

edit: as an added bonus, if you wanted to do this all on a sepereate table/sheet, the following would give you the list of top 5 names as well...

=IF(RANK(B3,$B$3:$B$15,0)>5,"",INDEX($A$3:$B$15,MATCH(LARGE($B$3:$B$15,ROW(A1)),$B$3:$B$15,0),1))

Edit2: scratch that, it's pulling the longest, not shortest. I will work on it again

**FDibbins** · 12-29-2012, 04:42 PM

ok, try this for the top 5...
=IF(OR(B3=0,RANK(B3,$B$3:$B$15,1)>5+COUNTIF($B$3:$B$15,0)),"",1)

**MWORSHAM** · 12-30-2012, 10:12 PM

FDibbins,

You are brilliant! That last formula works perfectly. Let me try and understand it though.

If that cell equals 0 or Rank that column (the one means it's ascending) and if the cell is ranked greater than 5, do nothing, if neither apply, add a 1 to this cell.

I don't fully understand the countif statement though. The way I'm reading it, it's saying to count the cells that have a value of zero but instead it's excluding them from the count. (which is exactly what I want it to do!) But how?

**FDibbins** · 12-31-2012, 01:38 AM

if you "leave" the zero's in, they will get considered in the ranking. to just exculde them would leave gaps in the ranking. So i added a count of all values that = zero, to 5 - so if there were no zeros, then you want the top 5, if there was 1 zero, then you want the top 6 (excluding the zero), 2 zeros - you need the top 7, and so on

Hope that explains it?