# Variable Pricing Formula

#### Harquor

##### New Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Tim_Excel_

##### Well-known Member
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:

#### Fluff

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

#### Finalfight40

##### Active Member
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)

Last edited:

#### Tim_Excel_

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

Last edited:

#### Tim_Excel_

##### Well-known Member
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

##### Active Member
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

##### New Member
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

##### New Member

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

##### Active Member

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: