Variable Pricing Formula

Harquor

New Member
Joined
Jun 22, 2006
Messages
36
Hello. I have a quote sheet that marks up my price by a certain percent. The way I have it set up now is for a 10% markup and I adjust it manually. C1 = .10, C2 is where I enter the cost, B2 = C1*C2, A2 = B2+C2.

I would like change this to vary the markup percent based on the cost. Ie. as the cost gets higher, I would like to lower the percentage at which I mark it up. For example: $1 - 200 is marked up 10%, $201 - $500 is marked up 8%, $501 - $1000 is marked up 5%, etc.

Is this possible to do so I am able to just enter a cost and excel will identify and adjust accordingly?
 

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.
Code:
IFS(<c2<201,0.1*c2, 200<c2<501,0.08*c2)
0 < C2 < 201 , 0.1 * C2 , 200 < C2 < 501 , 0.08 * C2)[FONT=Verdana]<c2<201,0.1*c2, 200<="" span="">[FONT=Verdana]<c2<501,0.1*c2)< span=""></c2<501,0.1*c2)<>[/FONT]</c2<201,0.1*c2,>[/FONT]

If you use the formula above and add the other markup ranges to it, you should get exactly what you want!


EDIT

Can't for the live of me figure out why, but the formula in my post keeps being deleted. So.. use the IFS() function.
Part of how the formula would look like within the brackets:
</c2<201,0.1*c2,><c2<201,0.1*c2, 200<c2<501,0.08*c2)
0 < C2 < 201,0.1*C2, 200 < C2 < 501, 0.08 * C2</c2<201,0.1*c2,>
 
Last edited:
Upvote 0
The board software interprets < & > as HTML tags so you need to add spaces either side of them.
 
Upvote 0
Hi Harquor

You can do this a few ways:

The first would be a nested if statement:

=IF(C2<=200, 0.1, IF(C2<=500 0.08, IF( ... and just carry on for however many conditions you want.

Another option would be to use a LOOKUP table:

Have something like this as a table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cost[/TD]
[TD]Increase[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]200.01[/TD]
[TD]0.08[/TD]
[/TR]
[TR]
[TD]500.01[/TD]
[TD]0.05[/TD]
[/TR]
[TR]
[TD]1000.01[/TD]
[TD]Something[/TD]
[/TR]
</tbody>[/TABLE]


and use =LOOKUP(C2, THIS WOULD BE THE TABLE)
 
Last edited:
Upvote 0
Hi Harquor

You can do this a few ways:

The first would be a nested if statement:

=IF(C2<=200, 0.1, IF(C2<=500 0.08, IF( ... and just carry on for however many conditions you want.


IF statements within IF statements get confusing rather quickly, I'd rather use the IFS function.
 
Upvote 0
IF statements within IF statements get confusing rather quickly, I'd rather use the IFS function.

True i do agree with you however when i post on any forum i always assume that not everyone has access to the IFS function.
 
Upvote 0
Thank you for your answer. I tried to enter this into a cell but it doesn't want to take? I'm sure it's something I'm doing wrong though. I have to apologize. I'm a real novice Excel user.
 
Upvote 0
Thank you for your help!

Would I put the table in a different sheet in the workbook? I'm a little new at this so I'm not exactly sure how to do this one.
 
Upvote 0
Thank you for your help!

Would I put the table in a different sheet in the workbook? I'm a little new at this so I'm not exactly sure how to do this one.

Hi Harquor

No problem. You should be able to put the table anywhere in the spreadsheet you want and just reference the table.

For the example i provided above, if you copied the table into cell A1 with Cost as a header then the table range would be A2:B5.

Also just a note that i made the 201 into 200.01 as this is currency and i don't like leaving those gaps but of course this is your work so change if needed.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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