Help with formula please...

[neilcarden]

Hi all,

Could anyone help with this formula?

http://www.neilcarden.pwp.blueyonder...s%20scheme.xls

I want to work out the percentage of a salary based on a target
achevied or not. Please have a look at the example.

If someone reaches 83% of target, then it falls into the 81-90% bracket
and they receive 2% of their salary, which populates in the bonus
acheived cell.

However if they acheive 93%, it falls under the 3% bracket etc...

Sound simple, but i can't fathom it out...

Thanks
Neil


--
neilcarden

[Don Guillett]

Without looking at your link
=if(a1>90,3,if(a1>80,2,etc))

--
Don Guillett
SalesAid Software
[email protected]
"neilcarden" <[email protected]> wrote in message
news:[email protected]...
>
> Hi all,
>
> Could anyone help with this formula?
>
> http://www.neilcarden.pwp.blueyonder...s%20scheme.xls
>
> I want to work out the percentage of a salary based on a target
> achevied or not. Please have a look at the example.
>
> If someone reaches 83% of target, then it falls into the 81-90% bracket
> and they receive 2% of their salary, which populates in the bonus
> acheived cell.
>
> However if they acheive 93%, it falls under the 3% bracket etc...
>
> Sound simple, but i can't fathom it out...
>
> Thanks
> Neil
>
>
> --
> neilcarden

[DCSwearingen]

I looked at your example.

Format cells C8:C10 as percent so to correlate to the sales percent. Title these as Minimum or whatever as a reminder. Only enter the minimum needed for the bonus, don't enter a range.

Formula in cell H7 is then =VLOOKUP(G4,C8:D10,2)*D4

Without having the last argument (True or False) in the Vlookup the table C8:D10 must be sorted ascending as the Vlookup will return the value in the range D8:D10 that corresponds to the largest value in C8:C10 that is less than the value found in cell G4.