Urgent Excel help needed PLEASE!!!!

[Monocock]

It might be basic stuff but I just can't get my head around how I create relevant formulae for the following. ANy help would be much appreciated.

I have two profit figures, one before making changes and one after making changes to a business structure.

I want to pay the person who makes the changes a banded reward structure.

0-10% = 5% of the uplift
11-20% = 10%
21-30% = 15%
31-40 = 20%
41+ = 25%

Whatever I do I just can't seem to get a suitable formula in place and I'm no Excel wizard.

Any help would be hugely appreciated

(For the sake of it and to make it easier to explain can we work on the basis that Profit 1 = Cell A1, profit 2 = Cell A2)

[Jhieminga]

So with Cell A1 = profit 1 and Cell A2 = profit 2, put this in a convenient cell to work out the profit percentage (lets take A3):
=((A2-A1)/A1)*100

You can format this to give a rounded figure, but that doesn't make a big difference to the rest of the calculation, just makes it easier to read. Now take another convenient cell (A4 in this case) and put this in there:
=IF(A3<=10;5;IF(A3<=20;10;IF(A3<=30;15;IF(A3<=40;20;25))))

It is a nested IF function that first checks if the profit is below or equal to 10 percent, if so it delivers the number 5, if not it moves to the next IF statement which checks if the profit is below or equal to 20 percent, delivering the number 10 if so, and so on. The last one checks if the profit is below 40 and delivers 20 if so, if not then it delivers the number 25.

Now you can take the result of this function and use this to calculate the amount you want to pay. Put this in A5 for example:
=(A4*0,01)*(A2-A1)

(If your computer is configured to use a dot as a decimal separator then change this in the last formula.)

You could of course combine everything into one big formula, for example you could take the first formula and substitute that for every instance of 'A3' in the next formula and do the same with the results (0,05*(A2-A1) instead of the number 5 and so on). By spreading the calculation across a few cells it is somewhat easier to follow and it may help avoid mistakes.

Edit: I just noticed that an extra space appears in the second formula, in the last bit there is a space between the 2 and the 0 of the number 20. I cannot get it out it seems to you'll have to edit that after copying the formula.

[bnt]

Another possibility is the Lookup Table, using the VLOOKUP or HLOOKUP functions. I'll assume VLOOKUP, since you gave the table in a vertical format.
The way it works is: set up a lookup table on a different part of the spreadsheet, with the upper values from your existing table, that looks something like this. Let's say you put the table in columns D and E, at the top:
0% 5%
10% 10%
21% 15%
31% 20%
41% 25%
So the table is $D$1:$E$5. Then the trick is to use the VLOOKUP formula with the "Approximate" flag set. I don't know exactly what you mean by "uplift", but I'll also assume it's the difference between the two profits: profits in A1 and A2, calculate the uplift in A3, then we do the lookup in A4 as follows:
=VLOOKUP(A3,$D$1:$E$5,2,TRUE)
The 2 means "return the value from column 2 of the table. I put the dollar signs in the table range to lock the references - that means you can copy the formula and the table references won't change. Note that I'm using valid % figures all the way here, so there's no need to multiply or divide by 100. The % figures in the table are translated to the actual numbers automatically e.g. 10% = 0.10. This assumes that the data you put in A1 and A2 are also in % format.
I've just tried this, and it seem to do the trick: when you change A3 from 10% to 11%, A4 changes from 5% to 10%, when A3 says 50%, A4 says 25%. I had to play with the lookup table figures a little, it didn't work correctly first time. Have a look at the VLOOKUP online help too.

[cdtaylor_nats]

The simplest way is a nested IF

=IF(A1<=10,5,IF(A1<=20,10,IF(A1<=30,15,20)))

where A1 is the profit percentage. This structure can be changed to allow as many bands as you want.

The structure of the statement is

IF(test,true value, false value)

[Monocock]

Thanks all

Really helpful