@ddunn2323
What you have is on some scales impressive.
What you have is also, for the outside observer, even someone with experience in compensation administration and associated matters**, hard to follow.
Your formula in column N, if I might offer this feedback, is a case study in the kind of formula that most Excel reference books warn against.
=IF(B2="","",(IF(M2=0%,K2,IF(AND(M2>0,M2<10%),K2+(((F2-K2)/10)*((M2-0%)*100)),IF(M2=10%,F2,IF(AND(M2>10%,M2<25%),F2+(((G2-F2)/15)*((M2-10%)*100)),0)))+IF(M2=25%,G2,IF(AND(M2>25%,M2<50%),G2+(((H2-G2)/25)*((M2-25%)*100)),IF(M2=50%,H2,IF(AND(M2>50%,M2<75%),H2+(((I2-H2)/25)*((M2-50%)*100)),0))))+IF(M2=75%,I2,IF(AND(M2>75%,M2<90%),I2+(((J2-I2)/15)*((M2-75%)*100)),IF(M2=90%,J2,IF(AND(M2>90%,M2<100%),J2+(((L2-J2)/10)*((M2-90%)*100)),IF(M2>=100%,L2*M2,0))))))))
- It's too long, for starters (it works! congratulations! But a formula at that length is almost impossible for someone else to maintain, and hard enough even for you a year or two from now, to say nothing of just five or six months);
- the nested IF..THEN conditions can be followed, but
- you've hard-coded many of the variables into the formula (meaning the conditions reflect current compensation rules, but if they change, if the percentile cut-offs change for example, you'd have to go in and revise (see maintainability, above)...
As to your question(s), you don't even mention column N, although it depends greatly on what's in M (which you do mention), and N in turn affects subsequent columns....
It would help me (and others I'm sure) help you if you could back up to "the bigger picture" by spelling out in words what the whole model is attempting to do, and then how each column in the sequence contributes to that.
As for column N itself, and that deeply nested set of IF...THEN, let me offer a model (created as a demonstration) of how a table (or set of tables) with VLOOKUP, can serve as a far more intelligible (and maintainable) way to accomplish the same result. (A warning: this demo does use the newly released function LET, and that requires the most current version of Excel).
- This sample is based on the US Federal tax tables, so the input (one's taxable income) is processed through a couple of conditions (filing status, level of income) to produce a single output.
- The analog to what one can do with IF statements is this; you use VLOOKUP to take a given value, look it up in a table, and then go across the columns of the table to select the appropriate "consequence." A single VLOOKUP can find "consequences" for multiple input conditions and the table is visible and readily changed for new fundamental rules.
- This same (tax example) spreadsheet is readily adapted to tax tables for subsequent years, new cutoffs, new percentages.
================
**I was the director of the HR and Payroll database system for a major corporation during my working career, so have seen and even created applications along these lines. I am retired now (for nearly 20 years), but do appreciate the challenges facing you here from having been there.
