Formula for adding correct percentage.

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
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 cost price in A2 to be cost price plus 20%.
If the cost is between £20 - £100 I would like a cost price in A2 to be cost price plus 15%.
If the cost is between £100 - £500 I would like a cost price in A2 to be cost price plus 10%.
Whats the formula please ?

Thanks Ted
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
On 2002-03-24 07:25, eddy wrote:
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 cost price in A2 to be cost price plus 20%.
If the cost is between £20 - £100 I would like a cost price in A2 to be cost price plus 15%.
If the cost is between £100 - £500 I would like a cost price in A2 to be cost price plus 10%.
Whats the formula please ?

Thanks Ted

Ted,

It's unclear what happens when

A10 = 1,
A10 > 500,

and if

A10 = 20, which mark-up should apply: 20% or 15% ?

Given these caveats, you can use either:

=A10*VLOOKUP(A10,{2,0.2;20,0.15;100,0.1},2)+A10

or, in a more controlled way:

=IF(AND(ISNUMBER(A10),A10>=2,A10<=500),A10*((VLOOKUP(A10,{2,0.2;20,0.15;100,0.1},2)+1)),"")

These formulas opt for 15% when A10 = 20 and 10% when A10 = 100.

Aladin
This message was edited by Aladin Akyurek on 2002-03-24 07:54
 
Upvote 0
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!
 
Upvote 0
GREAT !!!!!!
Thanks all. So many different ways! They all seem to work fine.
Thanks again for everyones efforts and time
Regards Eddy
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top