Trending February 2024 # Int In Excel (Formula, Examples) # Suggested March 2024 # Top 3 Popular

You are reading the article Int In Excel (Formula, Examples) updated in February 2024 on the website Cattuongwedding.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Int In Excel (Formula, Examples)

INT in Excel

INT in Excel is a very simple function used to convert any number into an integer value. Integer values are any number that is a whole number but can be a positive or negative number. Int function can consider any number, whether it is a decimal, fraction, or square root value, but in the end, we will be getting a whole number out of it.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

INT Formula in Excel:

Below is the INT Formula in Excel.

where

How to Use INT Function in Excel?

INT function in Excel is very simple and easy to use. Let us understand the working of the INT function in Excel by some INT Formula examples. INT function can be used as a worksheet function and VBA function.

You can download this INT Function Excel Template here – INT Function Excel Template

Example #1

The below-mentioned table contains a value in cell “C8”, i.e. 6.79, which is a positive number; I need to find out the nearest integer for 6.79 using the INT function in Excel.

Select the cell “E8,” where the INT function needs to be applied.

A dialog box appears where arguments (number) for the INT function need to be filled or entered.

i.e. =INT(C8).

It removes the decimal from the number and returns the integer part of the number, i.e. 6.

Example #2

The below-mentioned table contains a value in cell “C14”, i.e. -5.89, which is a negative number; I need to find out the nearest integer for -5.89 using the INT function in Excel. Select cell E14, where the INT function needs to be applied.

A dialog box appears where arguments (number) for the INT function need to be filled or entered.

i.e. =INT(C14)

It removes the decimal from the number and returns the integer part of the number, i.e. -6.

Example #3

In the below mention example, I have the date of birth ( 16th May 1982) in cell “J8” I need to calculate the age in cell “L8” using the INT function in Excel.

Before the INT function in Excel, let’s know about the YEARFRAC function; the YEARFRAC function returns a decimal value representing fractional years between two dates. I.e. Syntax is =YEARFRAC (start_date, end_date, [basis]). It returns the number of days between 2 dates as a year.

Here the INT function is integrated with the YEARFRAC function in cell “L8”.

YEARFRAC formula takes the date of birth and the current date (given by the TODAY function) and returns the output value as age in years.

i.e. =INT(YEARFRAC(J8,TODAY()))

It returns the output value i.e. 36 years.

Example #4

Usually, Excel stores the date value as a number, considering the date as an integer and the time as a decimal portion. If a cell contains a date and time as a combined value, you can only extract the date value using the INT function in Excel. Cell “P8” contains the date and time as a combined value. Here I need to extract the date value in cell “R8.”

Select the cell R8 where the INT function needs to be applied.

i.e. =INT(P8)

It removes a decimal portion from the date & time value and returns only the date portion as a number, where we need to discard the fraction value by formatting in the output value.

i.e. 11/12/18

Example #5

The below-mentioned table contains a value less than 1 in cell “H13”, i.e. 0.70, which is a positive number; I need to find out the nearest integer for decimal value, i.e. 0.70, using the INT function in Excel. Select cell I13 where the INT function needs to be applied.

A dialog box appears where arguments (number) for the INT function need to be filled or entered.

i.e. =INT(H13)

Here, it removes the decimal from the number and returns the integer part of the number, i.e. 0

Things to Remember

In the INT function, Positive numbers are rounded toward 0, while negative numbers are rounded away from 0. E.G. =INT(2.5) returns 2 and =INT(-2.5) returns -3.

Both INT() and TRUNC() functions are similar when applied to positive numbers; both can convert a value to its integer portion.

If any wrong type of argument is entered in the function’s syntax, it results in #VALUE! Error.

If the referred cell is not a valid or invalid reference in the INT function, it will return or result in #REF! Error.

#NAME? Error occurs when Excel does not recognize specific text in the formula of the INT function.

Recommended Articles

This has been a guide to INT Function in Excel. Here we discuss the INT Formula in Excel and how to use the INT function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

You're reading Int In Excel (Formula, Examples)

Count In Excel (Formula, Examples)

What is COUNT in Excel?

The COUNT in Excel is a function that counts the number of cells that consists of numeric values in a selected range and ignores all the other entries in the range. For example, the formula “=COUNT(A6:A20)” counts all the cells with numerical values (code number) in the cell range A6:A20, which corresponds to 7.

The COUNT function counts numeric values, including the date, time, percentages, negative numbers, formulas, and fractions.

Key Highlights

The COUNT in Excel is a completely programmed function that can be used for an array

The COUNT function family has a total of five variants- COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK

To count logical values, we use the COUNTA variant of the COUNT function family

To count numbers meeting certain criteria, we use either COUNTIF or COUNTIFS function in Excel

The function COUNT in Excel does not count formula errors and logical values

The COUNT function counts dates, too, as Microsoft Excel stores the dates as serial numbers

The function COUNT in Excel does not count the logical values- TRUE or FALSE

COUNT in Excel Syntax:

The syntax for the COUNT Function in Excel is-

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Explanation:

Value1: A required argument of the COUNT function indicates the first item or cell of the specified range.

Value2: It is an optional argument of the COUNT function in Excel that denotes the second set of cells or ranges we wish to count. Once we put the first Value1, all other values become optional.

Note: We can provide up to 256 values to the COUNT function.

The return of the COUNT function is always either zero or greater than zero.

How to use the COUNT in Excel?

Consider the examples below to understand how we can use the function COUNT in Excel.

You can download this COUNT in Excel Template here – COUNT in Excel Template

Example #1

Solution:

Step 1: Place the cursor in cell C7 and enter the formula,

=COUNT(A6:A20)

The above formula will count the numeric values in the given list, as shown below.

Step 2: Press the Enter key to get the below result

The selected range contains 15 values, but the COUNT function in Excel only counts the numerical values and ignores everything else. As a result, it returns 4 as the total number of numerical codes.

Example #2

The table below shows a list of dates. We want to count the total dates using the COUNT in Excel function.

Solution:

Step 1: Place the cursor in cell C8 and enter the formula,

=COUNT(A6:A12)

Step 2: Press the Enter key to get the below result,

The total number of selected cells is seven, but the COUNT function returned the value 5 because two dates in the given list are written in an incorrect format.

The below image shows the dates with incorrect format (highlighted in RED)

Example #3

The table below shows the IDs of five employees, as well as their wages and attendance for the first week of January 2023. If an employee is present, his attendance is marked as 1; if absent, his attendance is marked as A. We want to use the COUNT function in Excel to calculate the Employee’s total wages based on his weekly attendance.

Solution:

Step 1: Place the cursor in cell J6 and enter the formula,

=COUNT(C2:I2)

Step 2: Press the Enter key to get the Total no. of Paid Days as shown below

The COUNT in Excel function returns the Total no. of Paid Days as 6.

Now,

Step 3:  Place the cursor in cell K2 and enter the formula,

=B6/7*J6

Step 4: Press the Enter key to get the Total Wages of the week for Empl ID 1005

Step 5: Follow the same steps to get the Total wages for all the Emp IDs to get the below result

COUNT in Excel with IF condition

Syntax-

=IF(logical_test,[value_if_true],[value_if_false])

Example #4

Consider the above example of employees with IDs, wages, and weekly attendance. Using the COUNT and IF functions, we want to find eligible employees for Full Payment.

Solution:

Step 1: Place the cursor in cell L2 and enter the formula,

=IF (COUNT(C6:I6)=7,” Full Pay”, “Not Full Pay”)

COUNT(C6:I6): There are 7 working days in the week. Therefore, an employee present on all the days will be eligible for Full Payment.

Thus, the condition is written as COUNT(C6:I6)=7

Step 2: Press the Enter key to get the below result

Now,

Step 3: Enter the same formula in the remaining cells to get the below output

Combined with the IF condition, the COUNT function shows that only the person with Emp ID- 1D006 is eligible for Full Payment. Since all other employees were absent on one or the other day that week, they are not eligible for full payment.

Difference Between COUNT and COUNTA

The function COUNT in Excel counts the number of cells having numeric values within a cell range, whereas the COUNTA function counts the number of non-empty or blank cells within a given range.

The function COUNT in Excel counts numeric values and dates, whereas the function COUNTA counts all the cells within a range irrespective of the data type.

Syntax of COUNTA function is-

=COUNTA (value1, [value2], …)

Difference between COUNT and COUNTA with Example

The table below shows 10 rows with 6 numeric codes, 2 non-numeric codes, and 2 blank cells.

The COUNT function counts the number of cells with numeric codes and gives the result of 6.

The COUNTA function counts all the cells having codes, excludes the empty cells, and gives the result of 9.

Things to Remember

Only numerical values are counted in the COUNT function.

The COUNT function ignores empty cells, text and string values, and error values in the array.

If the COUNT function is applied to an empty range of cells, the result will always be zero.

If a text follows the number, COUNT ignores that value also. For example, =COUNT (“145 Number”) would return the result as 0.

If logical values such as TRUE or FALSE are supplied to the formula, the COUNT function will count these logical values.

The result will be zero if the same TRUE or FALSE is supplied in a range.

If you want the count of all the values in the given range, use COUNTA, which counts whatever comes it’s way.

Frequently Asked Questions (FAQs) Q1) How do I count cells in Excel?

Answer: In Excel, we can count cells using any of the COUNT function variants: COUNT, COUNTA, COUNTIF, and COUNTBLANK.

COUNT: To count cells with numeric values

COUNTA: To count non-empty cells

COUNTBLANK: To count blank or empty cells

COUNTIF: To count cells meeting specified criteria

Q2) What is the significance of the COUNT function in MS Excel Class 9?

Answer: We can use the COUNT() function to sum or add the number of cells that contain numbers in the specified cell. The count function can perform the complex calculation of adding numbers in a large data set, thus saving time and effort.

Q3) What is the main difference between COUNT and Countif?

The COUNT in Excel function counts the number of cells containing numeric data or entries, whereas the COUNTIF function counts the number of cells meeting the given criteria.

For example, the table below shows students’ Maths marks out of 50. Here, we use the COUNTIF function to count the number of students who have scored more than or equal to 35 and passed the test.

Recommended Articles

The above article is EDUCBA’s guide on using the function COUNT in Excel. For more information related to Excel formulas and functions, EDUCBA recommends the below articles.

Filter Shortcuts In Excel (Examples)

Filter Shortcuts in Excel

In this article, we will learn about Filter Shortcuts in Excel. there are different ways to access and apply filters in Excel. In the first way, we can select the headers first, then from the Data menu tab, select Filter Option under the Sort & Filter section. In a second way, we can apply the filter by pressing shortcut keys Alt + D + F + F simultaneously, and another way is by pressing shortcut keys Shift + Ctrl + L together to apply a filter in one go. Once the filter is applied, we can use other shortcut keys, such as the Alt + Down key, to get into the applied filter and select an option by pressing a shortcut key or navigation keys.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Use Filter Shortcuts in Excel?

Filter Shortcut in Excel is very simple and easy to use. Let’s understand the working of filter shortcuts in Excel with some examples.

You can download this Filter Shortcuts Excel Template here – Filter Shortcuts Excel Template

#1 – Toggle Autofilter in the ribbon Example #1

Here is a sample data on which we have to apply a filter.

You will see the name of the consumers who have taken Business Growth Loan.

Example #2

Now, if we want to see how many consumers have taken a loan of 10000, we can apply the filter as shown below:

#2 – Applying filter using the “Sort and Filter” option on the Home tab in the Editing Group

It can be found on the right side of the Ribbon in MS Excel.

Using the above data, here is how we can apply the filter:

Example #3

To filter the consumers who have taken a car loan.

Example #4

Now, if we need to filter some particular consumers, like their loan amount and their loan type, suppose we want to know the above details for Arjit and Dipa; we will apply the filter as below:

We will then be able to see the relevant details.

#3 – Filter the Excel data by shortcut key

We can rapidly press a shortcut key to apply the filter to our data. Together, we have to press Ctrl+shift+L.

Example #5

Here is a sample data on which we have applied the filter using the shortcut key.

Select the data and then press the shortcut key to apply the filter, i.e., Ctrl+shift+L.

If we want to filter the status of the Agents, like Active and Terminated, from the complete data, we will proceed as below:

Press OK, and we will see the Active and Terminated agents.

While applying filters on the selected criteria, we see other options as well as shown below:

On top of the filter drop-down, we see an option Sort A to Z; then we have Sort Z to A, Sort by color, and Text filters.

The data is filtered and sorted in order of Z to A.

Filter cells that Begin with or End with a specific text or character.

Filter cells that Contain or Do not Contain a particular text or symbol.

Filter cells that are either Equal to or Do not equal to a specific text or characters.

The data shows only Active and terminated status and not suspended.

Once you have applied the filter, you can disable it from that column as shown:

To remove a filter from the whole data, again press Ctrl+Shift+L.

Once you have filtered the data, copy it to any other worksheet or workbook. You can select the filtered data or filtered cells by pressing Ctrl + A. Then Press Ctrl+C to copy the filtered data, go to another worksheet/workbook, and paste the filtered data by pressing Ctrl+V.

Things to Remember about Filter Shortcuts in Excel

Some Excel filters can be used one at a time. For Example, you can filter a column by either value or color at a time and not with both options simultaneously.

For better results, avoid using different value types in a column. As for one column, one filter type is available. If a column contains different types of values, the filter will be applied for the high-frequency data. For example, if the data is in number in a column but is formatted as text, then the text filter will be applied, not the number filter.

Once you have filtered a column and pasted the data, remove a filter from that column because the next filter in a different column will not consider that filter, and hence the filtered data will be correct.

Recommended Articles

This has been a guide to Filter Shortcuts in Excel. Here we discuss how to use Excel filter shortcuts, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

Excel Formula – List Missing Numbers In A Sequence

A little while ago I received an email from Deborah asking what formula she can use to list missing numbers from a range.

Here is Deborah’s list of numbers:

You’ll notice they’re not sorted and there is a blank cell in the middle of the range. So, our formula needs to be able to handle blanks and an unsorted list.

And here she is:

=

SMALL(

IF(

COUNTIF($A$1:$A$7,ROW($1:$12)

)=0,ROW($1:$12),"")

,

ROW(A1))

It’s an array formula so you need to enter it with CTRL+SHIFT+ENTER.

Below you can see the results of the formula copied down column C. When there are no more missing numbers it returns a #NUM! error.

Tip: You can easily avoid the #NUM! errors by wrapping it in an IFERROR function if you prefer.

Count the numbers in the range A1:A7, that match the numbers generated by ROW($1:$12) i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, if they = 0 (i.e. if they’re missing), give me a list of them, if they’re not = 0 (i.e. not missing), give me “” (i.e. nothing), and in this cell just return the 1st smallest missing value.

The cell reference in the ROW(A1) part of the formula is relative, so as you copy the formula down column C, ROW(A1) becomes ROW(A2) which =2 and returns the second smallest missing number, ROW(A3) which is 3, returns the third smallest missing number and so on.

Functions Used in this Formula

SMALL

The SMALL function returns the smallest kth value in an array. e.g the first smallest, second smallest, third smallest and so on.

The syntax is SMALL(array,k)

ROW

This ROW formula: ROW(A1) returns the row number of a reference. In this case it would return 1.

This ROW formula: ROW($1:$12) returns an array of values from 1 to 12 like this {1;2;3;4;5;6;7;8;9;10;11;12}. It works this way because this is an array formula entered with CTRL+SHIFT+ENTER.

COUNTIF

The COUNTIF function is counting the numbers in the range A1:A7 that match the array of values returned by the ROW function {1;2;3;4;5;6;7;8;9;10;11;12}.

The syntax is: COUNTIF(range, criteria)

IF

The IF function tests for counts of 0, and if TRUE i.e. zero (remember if the count is zero it means the number is missing), it then returns the corresponding value from the resultant array generated by ROW($1:$12) i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, otherwise it returns nothing.

The syntax is: IF(logical_test, value_if_true, value_if_false)

Let’s Step Through How It Evaluates:

The key here is that array formulas evaluate on an array of items i.e. more than one item, within the one formula. Below you will see evidence of this as the formula evaluates.

=

SMALL(

IF(

COUNTIF($A$1:$A$7,ROW($1:$12)

)

=0,ROW($1:$12),"")

,ROW(A1))

Step 1 – The values in the range A1:A7 and ROW(1:12) are returned:

=

SMALL(

IF(

COUNTIF({1;7;5;8;;10;12},{1;2;3;4;5;6;7;8;9;10;11;12})

=0,ROW($1:$12),"")

,ROW(A1))

Step 2 – The COUNTIF then returns (a resultant array of) the counts of values in the array returned by the ROW(1:12) formula that are present in the range A1:A7:

=

SMALL(

IF(

{1;0;0;0;1;0;1;1;0;1;0;1}

)=0, ROW($1:$12),"")

,ROW(A1))

i.e. in the range A1:A7 there is 1 number 1 in the range, there are 0 number 2’s, 0 number 3’s etc.

Step 3 – The IF function’s logical test evaluates returning a TRUE where the number in the resultant array =0, and a FALSE where it doesn’t:

=

SMALL(

IF(

{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}

, {1;2;3;4;5;6;7;8;9;10;11;12},"")

,ROW(A1))

Step 4 – the IF function’s value_if_true and value_if_false evaluate:

=

SMALL(

{"";2;3;4; "";6;"";"";9; "";11;""}

,ROW(A1))

We now have a resultant array of the missing numbers.

Step 5 – The final ROW function evaluates to 1:

=

SMALL(

{"";2;3;4; "";6;"";"";9; "";11;""}

,1)

Step 6 – the SMALL function finds the 1st smallest value in the array:

=

2

Tip: the ROW function is used as a short cut to avoid having to type in an array constant. i.e. it’s quicker to type ROW($1:$12) than it is to type {1;2;3;4;5;6;7;8;9;10;11;12}

Note: This formula will ignore duplicate values in the list since it’s only looking for numbers in the range that have a count of 0, i.e. are missing.

Limitations of This Formula:

It uses the ROW function to generate a list of values that should be in Deborah’s range. It is therefore limited to the number of rows in your workbook. If you’re using Excel 2003 you’re limited to numbers in the range 1 to 65,536, and if you’re using Excel 2007 onwards you’re limited to number between 1 and 1,048,576.

It only works with whole numbers/integers.

Here is a clever VBA solution to find missing numbers that gets around some of the limitations above, plus the added bonus of allowing you to choose whether you want your list of missing values returned in a vertical or horizontal list.

Formula Challenge

What if your sequence of numbers were all negative. What formula would you use to find the missing negative numbers?

Thanks

I’d like to thank Deborah for asking this question. It reminded me that I should write about this formula.

I’d also like to thank Oscar Cronquist of Get Digital Help for sharing his formula.

Excel Formulas Cheat Sheet (Examples)

Excel Formulas Cheat Sheet

The cheat sheet of Excel formulas is like a customized worksheet that shows you how to use different functions and formulas in Excel. It includes shortcuts to quickly execute Excel functions and instructions on combining multiple functions in your way.

It can also include more complicated formulas that may be difficult to remember or use. Overall, an Excel Formulas Cheat Sheet is a handy tool for anyone who wants to improve using Excel.

Calculations Used in the Cheat Sheet of Excel Formulas

This article will cover Excel formulas that are most frequently used in calculations.

Let’s take some examples to understand the uses and workings of these functions.

You can download this Cheat Sheet Excel Formulas Template here – Cheat Sheet Excel Formulas Template

TEXT Functions in Excel

TEXT functions in Excel allow you to manage and format text within a cell and help perform various tasks, including converting dates to text, extracting specific characters from a string, and more.

To access this function, follow the below steps:

Go to the FORMULAS tab.

Refer to the below screenshot.

As we can see here, several string functions are available. The most commonly used text functions are RIGHT, LEFT, MID, CONCATENATE, LOWER, LEN, etc.

Example #1

We will understand how to apply the following text functions.

LEFT

RIGHT

MID

CONCATENATE

LEN

LOWER

We will use the following data to apply the text function:

Please refer to the following table for the result of text functions in Excel.

Sr. No.

Name

TEXT Function

Method of Function

Result

Description

1 Samuel Martin LEFT  =LEFT(A4,3) Sam It counts the string from the left. It returns the specified no. of characters.

2 Ronica Brave Joyce RIGHT  =RIGHT(A5,5) Joyce

It counts the string from the right and returns the specified no. of characters.

3 Phillip Studer MID  =MID(A6,5,9) lip Stude It counts the string from starting position and returns the no. of characters. Space also counts as a character.

4 Ian Smith CONCAT  =CONCATENATE(A7,A6) Ian SmithPhillip Studer It merges the strings mentioned and forms them as one string. Here we have passed A7 & A6 as an argument. If one needs to add space between the contents of A7 & A6, include (” “), wherever necessary i.e.,  =CONCATENATE(A7,” “,A6).

Ian Smith Phillip Studer

5 Fedrick Rodger LEN  =LEN(A8) 14 It counts the no. of characters and returns the length of the string.

6 Petrick Henderson LOWER  =LOWER(A9) Petrick Henderson It converts the string into lowercase.

STATISTICAL Functions in Excel

Statistical functions in Excel allow you to perform statistical analysis on data, like calculating measures of central tendency, variability, correlation, regression, and more.

To access this function, follow the below steps:

Go to the FORMULA tab.

Choose the Statistical Functions category.

It will open a drop-down list of functions.

The most commonly used statistical functions are MIN, MAX, COUNT, AVERAGE, MEDIAN, etc.

Example #2

We will understand how to apply the following statistical functions.

MAX

MIN

AVERAGE

COUNT

MEDIAN

We will use the following data for calculations:

Please refer to the table below to note the statistical function result in Excel.

Sr. No.

Number Values

Max

Min

Average

Count

Median

1 34

 =MAX(A4:A8)

 =MIN(A4:A8)

 =AVERAGE(A4:A8)

 =COUNT(A4:A8)

 =MEDIAN(A4:A8)

2 56 60 10 41 5 45

3 10 It returns the maximum value from the list. It returns the lower value from the list It returns the average of the list of values Counts the values in a list. It first arranges the values in increasing order and then returns the mid value. If the number of values is odd, it returns the mid value. If the chúng tôi values is even, then it takes the average of mid-two values.

DATE & TIME Functions in Excel

Date and time functions in Excel allow you to manage and format dates and times in cells. These functions can be helpful for various tasks, such as calculating the difference between two dates, extracting the month or day from a date, adding or subtracting time from a given date, and more.

To access this function, follow the below steps:

Go to the FORMULAS tab.

It will open a drop-down list of functions, as shown below.

Let’s understand this with some examples in the below screenshot.

Example #3

We will understand how to apply the following date and time functions.

DATE

NOW

TODAY

WEEKDAY

TIME

Please refer to the below table to understand how to use date and time functions in Excel.

Sr. No.

Function

Syntax

Example

Result

Explanation

1 DATE DATE(year,month,day)  =DATE(1996,4,24)

4/24/1996

This function returns the serial number of a date.

2 NOW NOW()  =NOW()

4/24/2023 10:12

This function returns the current date and time.

3 TODAY TODAY()  =TODAY()

4/24/2023

This function returns the current date as formatted.

4 WEEKDAY WEEKDAY(serial_no)  =WEEKDAY(D6)

2

This function returns the day of the week.

5 TIME TIME(hour,minute, second)  =TIME(1,15,60)

1:16 AM

It converts the hour, minute, and second to an Excel serial number in time format.

MATHEMATICAL Functions in Excel

Mathematical functions in Excel enable you to perform mathematical operations on data and carry out basic arithmetic operations like subtraction, addition, multiplication, and division and more complex operations like trigonometry, logarithms, and exponentials.

To access this function, follow the below steps:

Go to the FORMULAS tab.

It will open a drop-down list of functions, per the screenshot below.

We will understand the working of this function with some examples.

Example #4

We will understand how to apply the following mathematical functions.

SUM

PRODUCT

SUBTOTAL

RANDBETWEEN

SQRT

We will use the following data for calculations:

Given below is the application of the respective functions.

Sr. No.

Number

SUM

PRODUCT

SUBTOTAL

RANDBETWEEN

SQRT

1 11  =SUM(A4:A8)  =PRODUCT(A4:A8)  =SUBTOTAL(4,A4:A8)  =RANDBETWEEN(23,38)  =SQRT(A4)

2 12 65 360360 15 25 3.31662479

3 13 It returns the sum of all number values passed as an argument

It multiplies all the number values passed as an argument in the function It returns the subtotal in a list.

It returns the random number between the passed argument values.

It returns the square root of a number.

4 14 Here, we have passed the number values range A4:A8 as an argument. Here we have passed the number values range A4:A8 as an argument. Here we have passed the number values range A4:A8 as the second argument and want to see the maximum number value out of these, which comes under no. 4 passed as the first argument of this function.

Here we have passed two values, 23 as the bottom value and 38 as the top value. This function returns any random value between them.

Here we have passed A4 values as an argument. If we select the entire range, it will provide the square roots of every number.

5 15

Additional Excel Formulas for Daily Use

Formula

Data in Excel Cells

Example

Result

AVERAGE A10: 4

=AVERAGE(A2:A10)

This formula will calculate the average values in cells A2 to A10.

6

MAX A10: 4 =MAX(A2:A10)

This formula will return the highest value in cells A2 to A10.

10

MIN A10: 4 =MIN(A2:A10)

This formula will return the lowest value in cells A2 to A10.

2

COUNT A10: 4 =COUNT(A2:A10)

This formula will count the number of cells in cells A2 to A10 that contain numbers.

7

COUNTIF A5: 5

This formula will allow us to count the number of cells in the range A2:A5 greater than 7.

1

COUNTBLANK A10: 4 =COUNTBLANK(A2:A10)

This formula will help to count the number of blank cells in cells A2 to A10.

0

IF A2: 6

This formula will return “Yes” if the value in cell A2 exceeds 8 and “No” otherwise.

No

SUMIF B9: 6

This formula helps calculate the sum of values in cells B2 to B10, where the corresponding values in column A are more significant than 5.

22

VLOOKUP B4: 89 =VLOOKUP(“Paper”, A2:B4, 2, FALSE)

This formula will search for the value “Paper” in the first column (in table A2:B4) and return the value in the second column.

21

Things to Remember About Excel Formulas Cheat Sheet

And choose the function as per the requirement from the list.

Frequently used shortcut keys are:

CTRL+Z – Undo, etc.

Frequently Asked Questions

Answer: Excel has many valuable formulas, but some of the most common, helpful, and basic ones are:

SUM: Adds the value in several cells together.

AVERAGE: This function computes the average of a set of cells.

MAX: Finds the maximum value in a cell range.

MIN: Finds the minimum value in a cell range.

COUNT: Counts the number of cells containing numbers in a particular range.

COUNTA: Counts the number of cells other than blank ones in a range of cells.

IF: Tests a particular condition and returns one value if the given condition proves true and another if it turns out false.

ROUND: A number rounds to the specified number of digits.

CONCATENATE: Combines two or more strings of text or otherwise into a single string.

VLOOKUP: Finds a value in the first column of a table and returns a value from another column in the same row.

Answer: To merge cells in Excel, follow these steps:

Select the cells you need to merge. These cells should be adjacent and not contain any data you want to keep.

Your cells should now merge into one cell. If there were any text in the original cells, it would appear centered in the new merged cell.

Answer: You can use many keyboard shortcuts in Excel to save time and increase productivity. Below are some useful shortcuts that use the Ctrl key:

Ctrl + Y: Redo the last undone action.

Ctrl + A: Select all cells in a worksheet.

Ctrl + S: Save the current workbook.

Ctrl + N: Open a new workbook.

Ctrl + -: Delete the selected cells, rows, or columns.

Ctrl + Shift + =: Insert a new column or row into the worksheet.

Ctrl + Shift + $: Apply currency formatting to the selected cells.

Ctrl + Shift + #: Apply date formatting to the selected cells.

Ctrl + Shift + &: Apply border formatting to the selected cells.

Ctrl + Shift + ~: Apply general number formatting to the selected cells.

Recommended Articles

This has been a guide to Excel Formulas Cheat Sheets. Here we discuss the TEXT, STATISTICAL, DATE & TIME, and MATHEMATICAL Functions in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles.

What Is Product Cost? Formula, Examples & Calculator

What is Product Cost?

Product cost refers to the total amount of money a business spends on resources and the efforts needed to make a product during production. It includes direct expenses incurred in making the product (such as raw materials and labor) and indirect expenses (such as utilities, factory overhead).

However, it does not include the costs associated with selling the product or the administrative expenses of the business, as these expenses are not directly related to the production process. Simply put, it involves all the financial aspects helpful in bringing a product to life beyond just the visible direct expenses.

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

Imagine you want to bake a loaf of bread. The costs for it will include direct expenses, such as the ingredients like flour, yeast, and salt, and indirect expenses, like the electricity used to operate the oven and the overall costs of maintaining your kitchen. In this case, the product cost is the total money spent to bring your bread idea to reality.

Formula

The formula for calculating the cost of producing the product is,

Product Cost = Direct Labor + Direct Materials + Factory Overhead

We can also calculate the cost per unit, as it can help us set a suitable selling price for the final product. The formula to calculate cost per unit is,

Product Cost Per Unit = Total product cost / Number of producing units

Types

1. Direct Labor: Direct labor is the effort of employees to create the product or deliver the service. It includes:

Employees’ salaries

Medical or meal allowance benefits.

Example: A carpenter’s salary for creating a table from wood can come under the cost of direct labor.

2. Direct Material: Raw material or inventory used to create the final product can come under this category.

Example: The cost of wood and nails needed to create the table will come under direct material cost.

3. Factory Overhead: It refers to the costs that a company incurs for space, facilities, and support staff required to create a product. These costs come under indirect expenses. There is a formula to calculate this. It is,

Factory Overhead = Indirect Labor + Indirect Materials + Other Factory Overhead

Example: Overhead costs can include electricity costs or the salary of guard/security staff.

Product Costs on Financial Statements

Below we discuss the ways in which firms add the cost of the product to the three financial statements using the example of Apple Inc.

1. Income Statement

(Image Source: Apple Annual Report 2023 )

2. Balance Sheet

(Image Source: Apple Annual Report 2023 )

3. Cash Flow Statement

We do not enter the product costs directly in the cash flow statement. However, the cost of producing the product affects the working capital. As we add changes in working capital to the cash flow statement, we indirectly add the product cost to the cash flow statement.

So, when a company incurs product costs, the changes in working capital include changes in the accounts payable (due to purchases made), inventory levels (due to additions of product stock), and accounts receivable (pending payments from consumers). We add these in the operating activities section of the cash flow statement.

(Image Source: Apple Annual Report 2023 )

How to Calculate Product Cost?

Step 1: Determine the direct materials cost (add up the cost of all materials used to produce the product).

Step 2: Calculate the direct labor cost (multiply the hours of labor required to make the product by the labor rate per hour).

Step 3: Include the manufacturing overhead cost (calculate the overhead expenses such as rent, utilities, and equipment depreciation).

Step 4: Sum up the direct materials, direct labor, and overhead costs to get the total product cost.

Excel Examples

You can download this Product Cost Excel Template here – Product Cost Excel Template

Example #1

Direct materials =$10,000

Labor = $2,000

Overhead = $500

Other costs =$400

To determine the product cost, you have two options. First, you can calculate the product cost separately using the dedicated formula (Direct Labor + Direct Materials + Factory Overhead). Then, to find the cost per unit, you can use another formula (Total product cost / Number of producing units).

Alternatively, we have combined these two formulas into a single concise formula in this example. The formula is:

= (Direct Materials + Labor + Overhead + Other Costs) / Number of Units

= $13                     

Note: We have given screenshots of the Excel file so you can easily follow the calculations that we perform.

Hence, the total cost for creating 1,000 tables is $12,900, while the per-unit cost is $13.

Example #2

Macate bag manufacturing company produces a specific type of backpack. The company uses various materials and incurs direct labor and manufacturing overhead costs in the production process. Calculate the product cost for 6000 backpacks for the company based on the following information:

Direct materials = $45,000

Labor = $9,500

Overhead = $5,600

Other Costs = $4,000

1. As we already have the direct material, labor, and overhead values, let us use the cost per unit formula directly.

Product Cost = Direct Materials + Labor + Overhead + Other Costs

2. We calculate the product cost per unit using the following formula:

Product Cost Per Unit = Product Cost / Number of Units

Therefore, the cost per backpack for the bag manufacturing company is $11.

Note: This example provides a simplified calculation in a real-world scenario.

Example #3

Flour = 5 kg = cost per kilo = $10;

Cost for Flour = 5 x $10 = $50

Sugar = 3kg = cost per kilo = $13;

Cost for Sugar = 3 x $39

Others = $20

Additional Costs = $200

Step 1: First, we will calculate the raw materials cost using the following information:

Flour = $50

Sugar = $13

Others = $20

Step 2: Now, let’s calculate the labor cost using the following information:

Time taken to bake a packet of muffins = 1 hour

Employee pay per hour = $15

Units of muffins = 5

Step 3: Now that we have all the necessary details, let’s calculate the product’s cost.

Product Cost = Raw Material + Labor + Additional Cost

Step 4: Now, let us use the cost that we found to calculate the cost per unit. We can use the below formula for the same:

Product Cost Per Unit = Product Cost / Number of Units

Thus, the per unit cost of the product is $72.

Product Cost Calculator

Direct Labor Direct Materials Factory Overhead Product Cost =   Product Cost = Direct Labor + Direct Materials + Factory Overhead =

0

+

0

+

0

=

0

Advantages

It helps businesses set a profitable as well as affordable selling price for their products. The calculator can be complicated and inaccurate in some cases.

Companies can use it to know how much profit each product or product line makes. Paying more attention to this formula can reduce the profitability of your business.

It helps businesses identify areas where they can lower or optimize costs. As this formula only uses internal costs, it provides a limited perspective on the cost of production.

Product Vs. Period Cost

Particulars

Product Cost

Period Cost

Definition It is the total money the company spends on producing goods/ services. It is the total money that the company incurs in a specific period of time, such as a year or a month.

Examples It includes raw materials, direct labor, manufacturing overhead, packaging costs, etc.

Timing It considers the costs that are incurred during the production process. It focuses on a specific period of time, regardless of production.

Recording Companies record it as inventory at the beginning, and after selling the goods, they record it as costs of goods sold. Firms record these as expenses on the income statement.

Frequently Asked Questions (FAQs)

Answer: Calculating product cost is essential for:

Determining the profitability of a product

Making informed pricing decisions

Understanding the expenses associated with the production

Implementing cost control and budgeting.

Answer: Several factors can influence product cost, such as:

Fluctuations in raw material prices

Changes in labor rates

Variations in overhead expenses

Economies of scale

Production efficiency

Answer: Product cost refers to the expenses incurred in producing a specific product and is assigned to the inventory. On the other hand, period cost includes non-production expenses (e.g., sales and marketing expenses, administrative costs) incurred and recorded during the period they occur.

Answer: To reduce product costs, you can explore options such as:

Negotiating better prices with suppliers

Improving production efficiency

Optimizing resource utilization

Adopting cost-effective manufacturing processes

Implementing lean practices

Recommended Articles

Update the detailed information about Int In Excel (Formula, Examples) on the Cattuongwedding.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!