nested ifs 7 limit

[jackj]

I work for a contact centre in UK, keeps me off the streets. They offered me the opportunity ( I am an ex applications trainer) to do this call centre scoring system in excel and write an access database to produce scoring reports etc. I am not bad on applications including access, I like access, and thought it would be a breeze, until I came across excel's limited nested If functions. I have googled this, and seen a few suggestions which I tried, but not working for me.

well here is the brief from guys for the staff scorring system

Re your earlier message about the scoring table for the Productivity v quality chart. I'd like the basic scoring to be the same for all items as follows:-

Score
1 more than 15% lower than all CC average
2 between 10% and 15% lower than all CC average
3 between 8% and 10% lower than all CC average
4 between 5% and 8% lower than all CC average
5 between 2% and 5% lower than all CC average
6 less than 2% worse than all CC average
7 Equal to All CC average
8 up to 3% better than all CC average
9 between 3% and 10% better than all CC average
10 more than 10% better than all CC average

CC means national Call Centre Averages

He says "I've used the words 'better' and 'lower' for want of a better phrased because depending on what we are measuring 'better' could be a higher or lower number. For example for a quality measure such as security the higher the number, the better. However, for WAM error rate the lowest number would represent the best achievement."

Well that amounts to factors like call handling time scores, calls per hour scores, security pass levels, customer experience, etc etc, the wam error rate is how good or bad someone is at sending accurate emails to the relevant office. We get the figures anyway, so I have to do this scoring system.

I appear to have on my hands the excel 7 nested IFs limitation? And scores with "between" factors. I understand the Ifs logic.

I know "vlookup" might be the best way, but with all there conditions and comparisons I am finding this hard. So many logical Ifs still exist. I am no code man, they have probably chosen the wrong guy here. I know about applications but vlookup I cant get head around for this scenario, mental block, could someone give me a specific idea of the kind of vlookup formulas and vlookup commands i could use and what kind of columns to create? I understand IFS but not vlookup as applied to this as so many conditions exist.

many thanks if you can assist. The answer to this might be well googled, I notice many thousands on the internet asking similar questions about nested ifs in excel.



jackj

 

[shades]

Howdy, and welcome to the board. Actually, you can nest more than 7 IFs, but it involves some helper cells (one cell for each set of seven IFs); I've done as many as 18. And it works.

But, it is difficult to trouble shoot and maintain. So you would need to document the process thoroughly.

Lookup tables are the way to go, even with additional lookup tables. It might be better to define more clearly the limits of each. You can do multiple lookups better than multiple nested IFs.

Perhaps you can attach here on the forum just a mock-up of what the re-defined limits table(s) will be. No need to have everything all at once. Then we can look at some lookup functions for you.
________
DIGITAL VAPORIZER

 

[jackj]

Thanks for your reply

Hello I attached an example worksheet, struggled with IFs all day, but I reckon lookups are the way to go as you suggest.

I need some guidance on how to proceed as their is so many comparisons to work out.

I have attached a sample worksheet if that helps.

Any assistance greatly appreciated

 

[shades]

Okay, as a starting point, I used the Lookup worksheet and only cell set up the Calls per hour. Eventually you could leave the lookup array here and move the basic formulas in rows 2 and 3 to ScoringComp. I changed the name to take out spaces and shorten for easier setup.

You would need one lookup table like this for each of the categories.

Lookup table:

$B$5:O$13

(this is because the reference column will always change, so the lookup reference has to move with that)

This has the limits for each category (column A), the resultant number (Column B), and the percentage based on

Then, in Row 1, I put the formulas to bring in the numbers from ScoringComp

In C1

=ScoringComp!B$1

(then copied across)

In C2 (to determine actual percentage)

=(ScoringComp!B$30-ScoringComp!B$17)/ScoringComp!B$17

In C3 to look up score

=INDEX($B$5:$O$13,MATCH(C$2,C$5:C$13,TRUE),1)

Copy each of these over to Column O (notice that the last two columns have 0's, so division by 0 error).

So row 3 now gives you the correct score. Adjust percentages and limits as needed. Make another lookup table for Other Work Time, etc. These can all be done on the same worksheet, just in their own areas. I would probably set up a named range for each major lookup table, i.e. for the one above I would label it CallsPerHour and reference $B$5:$O$13, then use that name in the INDEX formula for the array.
________
Buy Easy Vape Vaporizer

 

[jackj]

Hello Shades, thanks a lot for that, given me a lot to think about, you obviously know your formulas!!

I will need to study the system you are using. At the moment though if the individual calls per hour is higher than the "average", I am getting a lower score rather than higher, probably the way I put it, you might have thought it was the reverse. I will also need to work out how to do the lookups for both higher and lower is best scores in individual scenario's

great, you have given me a valuable pointer, I will look into this index and matching thing and the rest, I dont confess to understanding it yet, but its amazing

many thanks for that.

jackj

 

[jackj]

Scoring system on Lookup

Hello there,

I have worked on this solution, I now have an understanding of the way it works, and I will certainly use this.

I am struggling though to get the scoring system return accurate values. The criteria has "between percentages" factors, which makes this awkward. I have attached another spreadsheet with the percentages I am trying to use in column A in the lookup worksheet.

The score percentage cell is returning the correct precentages, but I wonder if their is a way to make the scoring system return accurate score values according to the comples criterai involved. The negative returns need a large leyway percentage or it returns an NA for score 1 as you might see.

regards

jackj

 

[shades]

Hello there,

I am struggling though to get the scoring system return accurate values. The criteria has "between percentages" factors, which makes this awkward. I have attached another spreadsheet with the percentages I am trying to use in column A in the lookup worksheet.

The score percentage cell is returning the correct precentages, but I wonder if their is a way to make the scoring system return accurate score values according to the comples criterai involved. The negative returns need a large leyway percentage or it returns an NA for score 1 as you might see.

Not sure I understand what you mean by "return accurate score values".

Couple of notes on the use of MATCH (and Lookup formulas). 1) If TRUE is the last argument, then the formula returns if not an exact match, then the closet value less than that. 2) The #N/A for score 1, indicates that there was no exact match and there is no "other" category below it. Thus, you could modify the formula to account for that:

=IF(ISNA(INDEX($B$5:$O$13,MATCH(C$2,C$5:C$13,TRUE),1)),1,INDEX($B$5:$O$13,MATCH(C$2,C$5:C$13,TRUE),1))

Then you don't have to worry about the #NA.
________
Buy vaporizers

 

[jackj]

Thanks again for that, very helpful

You asked what i meant by "return accurate values". My score criteria includes "Between" values. For example a Score of "9" is awarded if Call Handling time is between 3 and 10 percent better thac average.

Your previous post helped me see what the problem is when you said "If TRUE is the last argument, then the formula returns if not an exact match, then the closet value less than that"

For example an exact match of 0% in this criteria returns a score of 7. But a slightly higher percentage than 0% will also return 7 even though it is in a higher scoring position of 8

Is it possible to arrange the formula to return the higher value, rather than the lower if not an exact match?

jackj

 

[shades]

Try moving the rank numbers up or down one cell (be sure that you range still includes everything needed.
________
TOYOTA R TRANSMISSION

 

[jackj]

Hi, tried that, also been googling this all day, but no joy yet. Possibly the scoring criteria is a bit to complex, some score combinations work and most others dont. When tested with different possibilities it generally doesnt work.

I have spent to many hours on this, but have learned a lot anyway, thats a plus, thanks for your kind efforts.

jackj

 

[shades]

I guess I would need more info on the scoring then to effectively change it. Sorry I couldn't help you more.

BTW, I will be traveling with limited access to internet this week, so perhaps others might have other ideas.
________
ULTIMATE FIGHTER

 

[jackj]

the lookup scoring system it is now working great.

Good News, it is working perfectly now. I just had to add more rows to the lookup with the same score, and just tweaked the percentages to focus on the correct score. I found Goal Seek useful to acheive this. But anyway I have tested every scenario and its working fine.

Many thanks for your help, I could not have done this without you-it is appreciated. I have atached an example. The Lookup looks a bit weird now, but it works, thats the main thing. I formated it as percentages which helps me see the actual percentages being returned to test it.

I have a lot of work to do on it yet as I have to add more ranges for each criteria, move things across and create named ranges, but I know what I am doing now which is half the battle.



jackj

 

[jackj]

with this problem I spent many hours tweaking the percentages until it worked. That was my job after you pointed me in the corrrect direction. I think, shades you will never respond to thanks? for your own reasons, I respect that. You knew if you gave me a pointer I would find the solution?. Does grateful thanks do anything for you mate? I sense you have a scottish spirit somehow, and I would like you to be my mentor, I will study. I used to be a well liked applications trainer in UK? High salary, but went through a phase when I drank too much. But I have avoided coding? But now getting interested.

Shades forums need personality, should be home from home for lonely geeks?

you take care ok?

Bond, James Bond..

Hey shades mesage me privately, I am also a skilled web designer, perhaps? regards to you and yours?

James, Macambridge

 

[shades]

with this problem I spent many hours tweaking the percentages until it worked. That was my job after you pointed me in the corrrect direction. I think, shades you will never respond to thanks? for your own reasons, I respect that. You knew if you gave me a pointer I would find the solution?. Does grateful thanks do anything for you mate? I sense you have a scottish spirit somehow, and I would like you to be my mentor, I will study. I used to be a well liked applications trainer in UK? High salary, but went through a phase when I drank too much. But I have avoided coding? But now getting interested.

Shades forums need personality, should be home from home for lonely geeks?

you take care ok?

Bond, James Bond..

Hey shades mesage me privately, I am also a skilled web designer, perhaps? regards to you and yours?

James, Macambridge

Ah, I just a have a few minutes on the net. What a thrill it is for me to see you take the direction and advice and make it work!! The joy of teaching! You have made my day!!!!

I am currently President of our Seminary, but primarily traveling and training those who are already pastors. For me, it is a delight to not just teach the material/theology, but to teach others how to learn on their own. It is exciting to see them come to the point of "Aha!" And so it is with Excel. I have been teaching Excel to users for the past 6 years; in fact, my last 2 months with that company (60,000 employees) was specifically to help other analysts development their Excel and analytical skills.

BTW, there many others here who are far more gifted than I. I just try to pass on what I can that I learned the hard way. When I started working at that company 8 years ago, I had never used Excel or Access, yet was hired as an analyst. Reason? I had a B.S. degree in Mathematics 40 years ago!

Old codgers can be useful, eh?

As for ethnic background, 50% Irish, 40% German, and the rest mongrel.

Thanks for the kind words. And wish you well as you continue on the project.
________
Iolite vaporizer