Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Variable Markup Formula

0

What is this formula to determine 4 levels of price mark-up of a product based on it's cost. Example:

     A                B               C                                                      D

Product         Cost     Marked-Up Product                        Mark-Up Levels

A Widget         .50            1.00                                               .01 - 50.00  =  200%

B Widget      55.00          96.25                                        50.01 - 100.00  = 175% 

C Widget    100.00         100.75                                     100.01 - 500.00  = 150% 

D Widget    500.00          675.00                                    501.00 +

The fomula is for column C to determine based on B cost which markup to use from column D and apply it to the original cost calculating the consumer price. The spread sheet I have has 786 rows of products to calculate from 4 markup levels.

Thank You

Answer
Discuss

Answers

0

For the mark-up levels, that needs to be stored in two columns and with the data like this:

D      E

0      %markup

50    %markup

100  %markup

500  %markup

Then use this formula in cell C1 and copy it down:

=VLOOKUP(B1,$D$1:$E$4,2)

This formula assumes that the data starts in row 1, that the cost is in column B and that the table with the markup is in column D and E, like I describe above.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login