Thanks:  0
Likes:  0

# Thread: Formula for adding correct percentage.

1. I need to produce a conditional sell price
dependant upont the cost price.
E.G. A10 Could contain a cost price between £2 and £500.
If the cost is between £2 - £20 I would like a sell price in A2 to be cost price plus 20%.
If the cost is between £20 - £100 I would like a sell price in A2 to be cost price plus 15%.
If the cost is between £100 - £500 I would like a sell price in A2 to be cost price plus 10%.
Whats the formula please ?

Thanks Ted

2. Hi Eddy

Not tested, but try:

=IF(A1>100,A1*0.1,IF(A1>20,A1*0.15,A1*0.2))

You may also want to look at a "Validation list" to prevent amoumts over 500 etc See Data>Validation

_________________
Kind Regards
Dave Hawley
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-24 07:42 ]

3. You can do such a calculation many ways.
First, a question. What do you want to do if cost is < 2 or > 500?

The formula ignores lower and upper factors.
Check the formula at the thresholds. What is the correct rate at 20, 100?

Other excellent solutions include:
- VLookup, Hlookup, or Lookup.
- If

=A10*((A10>0)*0.2-(A10>20)*0.05-(A10>100)*0.05)

4. Try this:

=IF(AND(A10<=20,A10>=2),A10+(A10*0.2),IF(AND(A10<=100,A10>20),A10+(A10*0.15),IF(AND(A10<=500,A10>100),A10+(A10*0.1),"Invalid Number")))

Tom

[ This Message was edited by: TsTom on 2002-03-24 07:47 ]

5. sorry

I did not add the original amount

=A10+A10*((A10>0)*0.2-(A10>20)*0.05-(A10>100)*0.05)

=IF(A10>100,A10*1.1,IF(AND(A10<=100,A10>20),A10*1.15,A10*1.2))

[ This Message was edited by: Dave Patton on 2002-03-24 07:54 ]

6. Hi Ted:
Here we go
=IF(cost>500,"NA",IF(cost>=100,cost*110%,IF(cost>=20,cost*115%,IF(cost>=2,cost*120%,"NA"))))

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

7. GREAT !!!!!!
Thanks all. So many different ways! They all seem to work fine.
Thanks again for everyones efforts and time
Regards Eddy

8. Oh bugger, I did the same as Dave Patton, I forgot to add the original amount. Just add one to the percentage. EG:

=IF(A1>100,A1*1.1,IF(A1>20,A1*1.15,A1*1.2))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•