Variable Pricing Formula

[Harquor]

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?

 

[Tim_Excel_]

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

 

[Fluff]

The board software interprets < & > as HTML tags so you need to add spaces either side of them.

 

[Finalfight40]

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:

Cost	Increase
1	0.1
200.01	0.08
500.01	0.05
1000.01	Something

<tbody>
</tbody>

and use =LOOKUP(C2, THIS WOULD BE THE TABLE)

 

[Tim_Excel_]

The board software interprets < & > as HTML tags so you need to add spaces either side of them.

I'm afraid adding leading and trailing spaced did nothing..

 

[Tim_Excel_]

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.

 

[Finalfight40]

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.

 

[Harquor]

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.

 

[Harquor]

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.

 

[Finalfight40]

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.