Formula for adding correct percentage.

eddy · Mar 24th, 2002, 08:26 AM

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

Dave Hawley · Mar 24th, 2002, 08:40 AM

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
OzGrid Business Applications
Microsoft Excel/VBA Training

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

Dave Patton · Mar 24th, 2002, 08:44 AM

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)

Tom Schreiner · Mar 24th, 2002, 08:45 AM

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 ]

Dave Patton · Mar 24th, 2002, 08:46 AM

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 ]

Yogi Anand · Mar 24th, 2002, 08:51 AM

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!

eddy · Mar 24th, 2002, 11:03 AM

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

Dave Hawley · Mar 24th, 2002, 06:35 PM

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))

Mar 24th, 2002, 08:51 AM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Ted: Here we go =IF(cost>500,"NA",IF(cost>=100,cost110%,IF(cost>=20,cost115%,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! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 24th, 2002, 08:26 AM	#1
eddy Board Regular Join Date: Mar 2002 Posts: 473	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

Mar 24th, 2002, 08:40 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Eddy Not tested, but try: =IF(A1>100,A10.1,IF(A1>20,A10.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 OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-03-24 07:42 ]

Mar 24th, 2002, 08:44 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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)

Mar 24th, 2002, 08:45 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Try this: =IF(AND(A10<=20,A10>=2),A10+(A100.2),IF(AND(A10<=100,A10>20),A10+(A100.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 ]

Mar 24th, 2002, 08:46 AM	#5
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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,A101.1,IF(AND(A10<=100,A10>20),A101.15,A10*1.2)) [ This Message was edited by: Dave Patton on 2002-03-24 07:54 ]

Mar 24th, 2002, 11:03 AM	#7
eddy Board Regular Join Date: Mar 2002 Posts: 473	GREAT !!!!!! Thanks all. So many different ways! They all seem to work fine. Thanks again for everyones efforts and time Regards Eddy

Mar 24th, 2002, 06:35 PM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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,A11.1,IF(A1>20,A11.15,A1*1.2))