Markup formula Excel

Get  your  own  personalized Online Excel Training Course to help you learn Excel effectively with your own tutor, personalized study plan and timetable.

If you want to learn Excel at your own pace and focus on what you need to know for your own  particular job, try our  personalized  online Excel Course, available on your laptop, tablet or phone 24/7.

Unlike most online Excel courses, our Online Excel course includes all levels of Excel  in one package.

  • Beginners Excel
  • Intermediate Excel
  • Advanced Microsoft Excel
  • Pivot tables

After you join, your tutor will get you to do a quick online assessment which will allow us to gauge your current skill levels.

From this,  your job position  and your daily usage of Excel, we will create a personalized program for you to follow –indicating which  Excel techniques and functions to concentrate on together with a timetable to follow. Your tutor will be available for you for questions, help and discipline  until you reach your Excel Goals.

In our online Excel course you can take an assessment at the end of each module to achieve up to 4 levels of certification.

Our online Excel course online covers Excel 2007,2010 & 2013.
This online Excel training course is available for individuals or group staff training where usage can be tracked by an administrator.

Cost: $157. Click Here to Learn More

In this article,we will see how to do markup formulas  in Excel,with lots of ready to use formulas to save you time.

We will also examine  other useful business calculation formulas like ‘How to calculate a running Percentage in Excel’,”how to do a running total”,etc. We finish up by learning how to round up numbers in business calculations.

Many of these questions have come from participants of our online excel courses at excelcoursesonline.com

How to create a basic Excel Markup formula?

 

In Excel , how can I create a basic excel Markup formula?

The basic formula is €Cost + €Markup = €Sell Price.

That is you add the markup amount to the cost of the item to get the selling Price.

See screenshot examples below

 

 

Percentage Calculation:
How to calculate Excel  Markup percentages on Cost?

How can I calculate an Excel Markup percentages on Cost? Below we have a markup on cost calculator.

It is based on the basic excel formula of Markups which is is €Cost + €Markup = €Sell Price
i.e is you add the markup amount to the cost of the item to get the selling Price.

 

 

So for example, the excel markup formula  in percentage terms is visible as you change the actual Markup amount.

As this calculator is based on cost, cost is the base amount i.e the figures are done as a function of cost.

Calculating Percentage:
How to calculate Excel percentage markup on selling price?

 

In Excel, how can I calculate Markup percentages on selling prices? Below we have a markup on selling price calculator.

Like the markup on cost calculator it is based on the basic excel  Markup formula which is is €Cost + €Markup = €Selling Price

That is you add the markup amount to the cost of the item to get the selling Price but this time the base is the selling price -it is 100% and the relationships are expressed in relation to selling price.

 

 

How to calculate a running Percentage in Excel?

 

Let’s look at an example on how to to calculate a percentage in Excel.

We have a table of salespeople in our company company and their sales amounts for a period. The total amount is easily calculated using the formula:

=SUM(B2:B7)
In order to calculate the running percentages for these amounts, use the following steps:

 

1. Enter the formula
=B2/$B$9
into cell C2. Format the cell to be displayed as percentage, with 1 decimal digit.

2. Copy cell C2 to the next cell (C3) using any way you prefer. Press F2 to edit cell C3.

3. At the end of the existing formula, add a plus and click cell C2 (the upper). Then press Ctrl+Enter.

4. Double click the fill handle in cell C3 to copy the formula it contains down to the other cells. We don’t want to add this formula to the Total row. That’s why we have left an empty row between the base table and its Total row.

 

How to create an Excel  running total formula ?

 

How can I create a running total formula in Excel?

 

We can use the formula in Cell C2

=SUM(A2,- B2) and in the cell below use

=SUM(C2,A3,-B3)

  1. To maintain the running balance, add a row for each new entry by doing the following:
    1. Type the amounts of your deposits and withdrawals into the empty rows directly below the existing data.

For instance, if you were using the example above, you would type the deposits into A4, A5, and so on, and the withdrawals into B4, B5, and so on.

  1. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.).

For instance, if you were using the example above, you would select cell C4 and then double-click its fill handle to extend the formula into all new rows that contain deposit and withdrawal values.

 

Alternative Method that  does not allow withdrawals.

Alternative formula that creates a running total in Excel?

Here we use the SUM function and the concept of absolute reference.

It is

=SUM(E$2:E2)

As this formula is copied down, only the second 2 changes, so you get

=SUM(E$2:E3)

=SUM(E$2:E4) etc

How to create an Excel  tiered commission plan?

In Excel, How can I create a tiered commission plan?

I need to calculate commissions due, based on a sliding scale- depending on the size of the sale as per the table below.

 

 

 

 

 

 

 

 

 

 

There are two ways to solve this problem, using the IF Function or the VLOOKUP Function.

To use the IF function in this situation, it is vitally important that you start looking for the largest category first.

For example, if a cell contains a sale of €18,000. Checking for C2>10,000 would return a TRUE, but checking for C2>1000 would be TRUE as well. You need to start checking for the largest value. If the sale is not larger than that value, then move on to checking for smaller values in turn relative to size.

If you are using <, less than, then you always check for the lowest value first.

=if(c2>10000, Do True,if(c2>75000,Do True, if(c2>50000, Do True,if(c2>10000, Do True, if(c2>1000,Do True,0))))

Even those Excel 2007, can handle over 100 nested IF Functions, they become difficult to manage. So for a neater solution, we can use the Vlookup Function instead.

For this to work, we need to reverse the order so that the largest lookup value appears at the end of the table. Add a start row with zero to handle the sales smaller than $1000

The second key point is to use TRUE as the last argument in the VLOOKUP.

Lets say we have a value of 12000, Excel will look for the value that is just smaller than 12000. So it will return the 2.75% since 10,000 is the level just smaller than $50,000.

 

How to count items in a list based on multiple conditions?

 

In many cases you need to count cells only if two or more criteria, based on the cells that are being counted or based on other corresponding cells, are met. For example, what if you have the table of the below screenshot, and you have to answer the questions in column G?

The solution is to use the COUNTIFS function.

This function accepts pairs of arguments, with the first argument in each pair to be the range cells of which will be counted, and the second argument in each pair to be one criterion that should be met.
So, for the first question, the formula will be:

=COUNTIFS(D2:D31, “>=2000”, D2:D31, “<=3000”)

 


Without the COUNTIFS function, you should count (using COUNTIF) the amounts that are greater than 2000 and then substract the number of the amounts that are less than or equal to 3000:
=COUNTIF(D2:D31, “>=2000”) – COUNTIF(D2:D31, “<=3000”)

Or, you could use an array formula, such as the one that follows (remember to use Ctrl+Shift+Enter to close the edit mode):

=SUM((D2:D31 >=2000)*(D2:D31 <= 3000))

How many sales are made by Abner that are greater than 4000?

Use the following formula to find it out:

=COUNTIFS(A2:A31, “Abner”, D2:D31, “>4000”)

 

 

Similarly, the formula for the third question is:

=COUNTIFS(A2:A31, “Flugelhart”, B2:B31, “Accessories”)

 

 

The fourth question, however, includes an OR logic. We want the number of sales with category “Games”, made by Abner OR by Gallaway. We have to combine two COUNTIFS to find this number, as in the following formula:
=COUNTIFS(A2:A31, “Abner”, B2:B31, “Games”) + COUNTIFS(A2:A31, “Gallaway”, B2:B31, “Games”)

 

 

Similarly, the next question (Q5) needs the following formula to get an answer:

=COUNTIFS(C2:C31, “Mobile”, B2:B31, “Accessories”) + COUNTIFS(C2:C31, “Mobile”, B2:B31, “Games”)

 

 

The final question needs a combination of two COUNTIFS (to implement the OR logic) with 4 pairs each (implementing the AND logic):
=COUNTIFS(C2:C31, “PC”, B2:B31, “Gadgets”, D2:D31, “> 3000”, D2:D31, “<6000”) + COUNTIFS(C2:C31, “PC”, B2:B31, “Accessories”, D2:D31, “>3000”, D2:D31, “<6000”)

 

How to round numbers in Excel?

 

We can do this by using the Round Funсtion.This function  returns а number rounded tο а specified number οf digits.

Its syntax is

Round Function: Round ( nυmber, digіts )

Where  Numbөr iѕ the number to roυnd аnd Digits іs tһe number of dіgits tο rοund tһe numbeг to.

Here іs an example:

With reference tο the spreаdsheet above

= Round ( A1, 0 ) would return 663

= Rοund ( A1, 1 ) would гeturn 662.8

= Rοund ( A2, -1 ) would return 50

Also

= Rοund ( 55.1, -1 ) would return 60

= Round (-23.67, 1 ) woυld retuгn -23.7

How to RoundUp and RoundDown numbers using Excel?

 

The RoundUp oг RoundDown Functіons cаn be υsed instead οf the Round Function.

The RoundUp Functiοn alwayѕ rounds uр the νalue sрecified by the “number” argument wһere aѕ the RoundDown Function alwаys rοunds the vаlue down.

In the exаmple below, Cell A3 сontains the value οf the Mathematical Constant “рi” (wіth nine decimаl places dіsplayed wһen tһe сolumn iѕ widened) using Excel’s PI Function.

= PI ( )

The RoundUP and RoundDown Functіons in the Cell range B3:B10 round thіs number uр and down to νarious decimal places.

 

Cell B3, the ROUND functions rounds off the νalue οf “pi”, to 3 becаuse 0 (zero) іs specified aѕ the “num_digіts” argument οf itѕ ROUND functіon (causing Excel to rοund tһe valυe to tһe neareѕt integers).

So if PI equals 3.14159265358979

then

Roundup(A3,4) will give a result of 3.1416

this is so because the fifth number in PI is 9

But Rounddown(A3, 4) will give 3.1415

The 9 this time is rounded down to 5.

How to round prices up or down to the nearest $10 ?

 

In Eхcel, the FLOOR and CEILING Functiοns can be used tο round uр prices to the nearest value.

Here iѕ the Function Syntax:

=FLOOR (nυmber, Significance)
=CEILING (number, significance)

Let’s take €13.32 for example and we need tο round it dοwn to tһe nearest vаlue divisible bү €10, tһe FLOOR function would read.

=FLOOR (13.32,10)

will give a result of €10

 

 

On the otherhand, the Ceiling Function can be used tο round οur priсes υp tο the neаrest €10 аs follows:

= CEILING (13.32, 10)

will give a result of €20

How to find the total number of all possible combinations for a given number of items..?

 

The COMBIN function is used to find the total number of all possible combinations for a given number of items.

The syntax for this function is:

COMBIN(number_of_items, number_of_group_members)

Both it’s arguments are mandatory. The first argument, number_of_items, represents the total number of items, while the second argument, number_of_group_members, represents the number of items that will “compose” each possible group.

 

Let’s clarify this with an example.

The function:    =COMBIN(7,2)

will return the total number of possible groups of two items (members) for a total of 7 items.

.

Example 1:

We want to find out how many possible ways there are to combine 7 items in groups of two items each. We will use the formula:

=COMBIN(7, 2)

As you can see, the result is 21. Let’s try to find out “manually”:

We have 7 letters, a, b, c, d, e, f and g. We can combine them in 2-member groups as follows:

ab, ac, ad, ae, af, ag,

bc, bd, be, bf, bg,

cd, ce, cf, cg,

de, df, dg,

ef, eg,fg,

Please Note:

keep in mind  that we have not to use both ab and ba groups, since in combinations the order is not significant (like in permutations). So, these two combinations, ab and ba are equivalent from COMBIN perspective and they are considered the same group.

Example 2:

How many possible combinations are there of  26 letters as subsets of 2 letters each? As subsets of 3 letters?

The following sheet gives the answers:

 

 

________________________________________________________________________________

Leave a Reply