If help - calculating percentage of a fee

[Feets]

Hi,

I would be really grateful if somebody could give me a hand writing an If formula. I'm okay at basic excel stuff but I haven't done If formulas before and this seems pretty complicated to me...

In Cell E3 I'm going to put a price. I want Cell F3 to work out a percentage of that price.

• If the price in E3 is between £0.01-£150.00 I need F3 to work out 9.9% of the value from £0.01-£150.00.
  
• If the price in E3 is between £150.01-£350.00 I need F3 to work out 7.9% of the remaining match value balance (£150.01-£350.00) plus 9.9% of the value from £0.01-£150.00
  
• If the price in E3 is between £350.01-£1,000.00 I need F3 to work out 5.9% of the remaining match value balance (£350.01-£1,000.00), plus 7.9% of the value from (£150.01-£350.00), plus 9.9% of the value from (£0.01-£150.00)

I can do the first step but I'm having a nightmare figuring out how to do the next two steps. Can anybody help?

Thank you

[davesexcel]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[drakk]

and IF statement works in the following manner

IF ( CONDITION , if answer is true do this , if answer is false do this )

You can put if statements inside eachother as well

So what you'd want is something like

If(E3>350,formula for this scenario,If(e3>150,formula for this scenario,formula for first scenario))

What this will effectively do is say i f e3 is more than 350 do this otherwise check if its more than 150, if it is do this otherwise do the other thing

[Special-K]

Deleted...........

[Feets]

I thought that this formula worked (thanks Special-K) but when I work it out myself I come up with a different answer. Could someone double check this?

=(E3>=350.01)*(E3-350.01)*5.9%+(E3>=150.01)*(E3-150.01)*7.9%+(E3>=0.01)*(E3-0.01)*9.9%

Now, if I enter 150 into E3, F3 tells me that 9.9% is £14.85. So far so good. But if I enter 200 into E3, F3 gives £23.75, which isn't the figure I get when I work it out myself.

9.9% of 150 is £14.85. 7.9% of the remaining £49.99 is £3.95. So the total that F3 should say is £18.80.

Can somebody tell me where it's going wrong?

Thanks