Slidng (?) formula calculation

[Andon3008]

Hi

I REALLY need some help with writing a formula to calculate a percentage of a subcontractor invoice, depending on the value.

Basically the formula needs to calculate if the invoice comes between:

£0 - £1000, 30% to be charged
£1001 - £2000, 25%
£2001 - £5000, 20%
£5001 - £10000, 15%
£10001 + 10%
(these are the only criteria there can be)

I've been trying to work it out on and off for about 7 hrs but I need to have it finished and in a report by the end of this week. I think i'm over complicating what i need to do

(I'm not an Excel novice but I'm not as fab as I thought I was )

Any help would be really appreciated.

Many thanks
Donna

[Richard Buttrey]

Hi,

A couple of ways at least. Either using VLOOKUP and a 2 column table of £ & % or directly with a formula like

=A1*IF(A1<=1000,30%,IF(A1<=2000,25%,IF(A1<=5000,20%,IF(A1<=10000,15%,10%))))

Regards