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?
 

Some videos you may like

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
Joined
Jul 12, 2016
Messages
512
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
Joined
Jun 12, 2014
Messages
35,908
Office Version
365
Platform
Windows
The board software interprets < & > as HTML tags so you need to add spaces either side of them.
 

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
365
Platform
Windows
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:

CostIncrease
10.1
200.010.08
500.010.05
1000.01Something

<tbody>
</tbody>


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

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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..
 
Last edited:

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
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
Joined
Apr 24, 2018
Messages
273
Office Version
365
Platform
Windows
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
Joined
Jun 22, 2006
Messages
36
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
Joined
Jun 22, 2006
Messages
36
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

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
365
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,035
Messages
5,411,995
Members
403,407
Latest member
craigey1

This Week's Hot Topics

Top