# Solved: Excel formula to calculate commission



## igofar (Jan 16, 2008)

Here is what I am trying to do.
I need to calculate a commission based upon the following scenario.
(5% of the first $100,000 and 3% on the remaining balance.)
I need to create this formula so it can be used on numbers up to 7 digits and be able to adjust the percent amounts up to 2 digits.

Any takers??

Thanks in advance,

Brad


----------



## The Villan (Feb 20, 2006)

Try this attached example.

I have protected the spreadsheet. The yellow cells can be changed for what if scenarios. The blue cell is protected. To turn protection off Tools, Protection, Unprotect sheet. There isn't a password.

The formula can be copied down, without the need to alter it..


----------



## igofar (Jan 16, 2008)

Thanks Villan, that solves my problem perfectly.
I thought I had a handle on Excel but I would have NEVER figured that formula out.


Brad (humbled yet again!)


----------



## The Villan (Feb 20, 2006)

Glad it worked Brad :up:


----------



## jimr381 (Jul 20, 2007)

I wanted to try tackling this a little differently. Here is a little shorter variant as well.


----------



## The Villan (Feb 20, 2006)

Only one comment there Jim.
I have always taught people not to put variables into formulas. If the 100000 changes to 120000, then the formulas have to be changed and that is very bad practice.
In my example, if 100000 changes to 120000 then the only cell that needs to change is H2. That leaves the integrity of the formulas intact.
Whats more people can do what if scenarios, whereas they can't with yours.

Other than that no comment.


----------



## igofar (Jan 16, 2008)

Here is a working copy of what I am trying to accomplish.

I just need to "fancy" it up a bit!!


Thanks,


Brad


----------



## The Villan (Feb 20, 2006)

OK, I have had a look at that.

My comments
You must get out of the habit of putting variables into formulas. It is very bad practice. If you have to change those variables, you will have to alter all the formulas and from an audit point of view, means that you could finish up with lots of errors.
Have a look at the version I have uploaded. You will see that I have put the variables in a seperate location and referred to them in the formulas.
I have also used relative and absolutes ($) in the formulas, so that the first formula in cell B3 can be copied all the way down to D23 without having to make any changes.
The same applies with the formula E3 which can be coiped down to J23 without having to make any changes.
Its very important that you learn to adopt theses sort of procedures as in the long run, you will save yourself hours of work, and what is more important, run less risk of having spreadsheets with loads of errors in.
Input cells are colour coded light yellow to make it easy to know where you can make changes, and the formula cells are colour coded light blue so that you know that you know not to type in those areas.
If you stick to the same sort of colour coding through all your spreadsheets, it will pay big dividends.
Likewise, you should be protecting cells that have formulas in, so that you know that you can always rely on the results, providing your raw data is input correctly.

I hope that helps.

Good luck with it.


----------



## jimr381 (Jul 20, 2007)

I as well used absolute and relative cell references within the formulas. I thought the 100000 was a constant and not a variable and as such I streamlined the formula down to that. That easily could have been replaced with cell references though for the change over amount for the percentages. How does your formula do something better than the one I posted? It does seem to be a little more bloated and less streamlined than mine?

I used your template to trim down the function again. It is one singles If statement instead of multiple nested functions. In class and yes I do teach this stuff I teach towards having a more streamlined function.

This is not a competition I am just trying to help him out. If my function is lacking in some way please feel free to enlighten me to it and I will not it for future reference. I am always open for constructive criticism and a new way of looking at things as well.


----------



## The Villan (Feb 20, 2006)

Jim
It was more about putting variab;es in formulas. Whilst at the moment the 100000 may seem to be constant, it could change overnight, if some bright spark decides that 100000 is too high and drops it say to 80000. Then the formulas need to be changed and that is not good practice. I see I made an error in my original attempt by putting a variable in the formula, however that was an oversight on my part.
The newer version, has been altered based on what Igofar did in post #7.

I have no problem with you trimming the formula Jim, thats what we are here for.


----------

