[amyowen]

Hi everyone, I've just started my business studies degree and an getting to grips with using excel properly.

I've uploaded the spreadsheet I'm working on. I'm trying to work out the discount values in column E. If anyone can help I'd really appreciate it, I'm hoping it's just my syntax that's wrong but I have no idea.

To work out the discount you apply the following;

IF VALUE IS LESS THAN £100 THEN NO DISCOUNT IS GIVEN
IF VALUE IS £100 OR ABOVE, BUT LESS THAN £200 THEN 33.333% DISCOUNT IS GIVEN
IF VALUE IS £200 OR ABOVE THEN 55.555% DISCOUNT IS GIVEN.

Cheers guys/girls

Amy (newbie)

[RickJ]

I see that you have a value error in those fields. I've pointed this out since it was not evident in your post - and clarified the problem in the subject line. We've got a couple good Excel folks here who may pipe in shortly.

[colbtech]

Try this when entering these sort of formulae I find it easier to read/understand if you start with the greatest and work down. See attached ss14a.zip

[ScottGem]

Assuming that the percentage is the actual amount discounted and the value you are discounting is in Cell A2 then in Cell B2 you would put:

=IF(A2<100,0,IF(A2>=200,A2*.55555,A2*.33333))

You can then copy that formula down the B column as far as you have values in A.

However, I would do it slightly differently. Rather than hard code the discounts, I would put then in cells somewhere else in your spreadsheet. Lets say AA1 and AB1. I would then change the formula to:

=IF(A2<100,0,IF(A2>=200,A2*$AA$1,A2*$AB$1))

In that way you could change the discount rates by just changing the values in those cells and wouldn't have to change the formula.

One point I forgot to explain, the reason I sues the $ in the cell addresses for the discounts was because of Absolute vs Relative addressing. The $ indicates that the address is absolute so the formula always looks at the same cell no matter where you copy it to. With relative addresses, the address changes relative to the cell you copy it to.

[colbtech]

However, I would do it slightly differently. Rather than hard code the discounts, I would put then in cells somewhere else in your spreadsheet. Lets say AA1 and AB1. i would then change the formula to:

Good call on that.

[amyowen]

Thanks for your help, everyone.

I had an exam on spreadsheets and passed :D

Much appreciated

Amy

[ScottGem]

Thanks for letting us know.