# TRUNC Function in Excel

### ðŸ“Œ Part 1: Introduction

Definition: The TRUNC function is a tool in Excel used to truncate a number to an integer by removing the fractional component.

Purpose: It is often utilized in business and financial calculations where fractional numbers are not desired. For instance, rounding off might not be appropriate when determining quantities, so truncating becomes necessary.

Syntax & Arguments:

syntax
`=TRUNC(number, [num_digits]) `

Explain the Arguments:

• number: This is the value you want to truncate.
• num_digits (optional): Specifies the precision of the truncation. If omitted, it defaults to zero.

Return Value: Returns a truncated number based on the specified precision.

Remarks: Unlike the ROUND function, TRUNC cuts off the decimal without rounding it.

### ðŸ“Œ Part 2: Examples

#### Example 1: Truncating Sales Data

Purpose: To truncate sales data to whole numbers, aiding in inventory or whole unit sales calculations.

ABC
1Sales (Units)FormulaResult
223.45=TRUNC(A2)23
317.89=TRUNC(A3)17
430.11=TRUNC(A4)30

Explanation: When calculating total sales units, working with whole numbers is essential to ensure accurate inventory and sales representation.

#### Example 2: Truncating Financial Data

Purpose: To truncate financial data for a budget forecast, removing cents.

ABC
1Projected IncomeFormulaResult
2\$4,523.67=TRUNC(A2)\$4,523
3\$3,212.49=TRUNC(A3)\$3,212
4\$6,700.15=TRUNC(A4)\$6,700

Explanation: When preparing a budget, businesses might want to disregard cents for simplicity, making the TRUNC function especially useful.

#### Example 3: Truncating Employee Hours

Purpose: To calculate total hours worked by employees without considering minutes.

ABC
1Hours WorkedFormulaResult
28.5=TRUNC(A2)8
39.75=TRUNC(A3)9
47.33=TRUNC(A4)7

Explanation: Truncating to whole numbers can simplify the calculation process when assessing employee hours for pay, especially if paid hourly.

#### Example 4: Truncating Product Ratings

Purpose: To analyze customer product ratings to the nearest whole number.

ABC
1Product RatingFormulaResult
24.2=TRUNC(A2)4
33.8=TRUNC(A3)3
44.9=TRUNC(A4)4

Explanation: For a high-level analysis, businesses might want to review ratings without the decimal values for simplicity and clarity.

#### Example 5: Truncating Monthly Expenses

Purpose: View monthly expenses to the nearest dollar for a quick overview.

ABC
1Monthly ExpenseFormulaResult
2\$1,235.67=TRUNC(A2)\$1,235
3\$812.45=TRUNC(A3)\$812
4\$2,500.99=TRUNC(A4)\$2,500

Explanation: For a quick financial overview, truncating to whole numbers provides a cleaner, more straightforward view of monthly expenses.

#### Example 6: Sales Bonus Calculation

Purpose: Determine bonus amounts for salespersons based on sales. If the sales exceed \$5000, they get 10% of the truncated sales as a bonus.

ABC
1SalesFormulaBonus
2\$6,230.55=IF(A2>5000, TRUNC(A2)*0.1, 0)\$623.00
3\$4,800.25=IF(A3>5000, TRUNC(A3)*0.1, 0)\$0.00
4\$5,999.99=IF(A4>5000, TRUNC(A4)*0.1, 0)\$599.00

Explanation: By truncating the sales amount, you ensure the bonus is calculated on a whole number, avoiding minor discrepancies due to decimals. Only those who’ve made sales over \$5000 earn a bonus.

#### Example 7: Tax Calculation

Purpose: Calculate tax amount based on a truncated selling price and a tax rate.

ABC
1PriceFormulaTax
2\$120.75=TRUNC(A2)*0.07\$8.40
3\$250.50=TRUNC(A3)*0.07\$17.50
4\$99.99=TRUNC(A4)*0.07\$6.93

Explanation: For simplicity, businesses may apply tax on the truncated price of items. This might streamline accounting processes, especially for bulk calculations.

#### Example 8: Discounts on Bulk Purchases

Purpose: Apply a discount if the number of items bought exceeds 10. Use truncated prices for the discount.

ABCD
1Items BoughtPriceFormulaDiscounted Price
215\$12.40=IF(A2>10, TRUNC(B2)*0.9, B2)\$11.16
38\$15.30=IF(A3>10, TRUNC(B3)*0.9, B3)\$15.30
412\$20.99=IF(A4>10, TRUNC(B4)*0.9, B4)\$18.89

Explanation: Offering discounts on bulk purchases can incentivize customers to buy more. Using the TRUNC function ensures the discount is applied to a rounded-off price.

#### Example 9: Commission on Sales

Purpose: Calculate commission based on sales. Use truncated sales and the SUM function to determine the total commission.

ABC
1SalesFormulaCommission
2\$300.50=TRUNC(A2)*0.05\$15.00
3\$400.75=TRUNC(A3)*0.05\$20.00
4\$500.99=TRUNC(A4)*0.05\$25.00
5Total Commission=SUM(C2:C4)\$60.00

Explanation: Sales commissions are a significant expense for businesses. Businesses can simplify commission calculations by truncating the sales amount before applying the commission percentage.

#### Example 10: Rented Days for Equipment

Purpose: Calculate rental charges for equipment. If the equipment is rented for more than half a day (over 12 hours), charge for a full day using the truncated hours.

ABC
1Hours RentedFormulaCharge
28.5=TRUNC(A2)*10\$80.00
315.3=TRUNC(A3)*10\$150.00
410.9=TRUNC(A4)*10\$100.00

Explanation: Equipment rental businesses might charge based on a full-hour system. Thus, truncating the hours ensures accurate and streamlined billing.

#### Example 11: Product Quality Rating

Purpose: Assign a rating based on quality scores. If the truncated quality score exceeds 4, rate it as ‘High’; otherwise, it is ‘Low’.

ABC
1Quality ScoreFormulaRating
23.8=IF(TRUNC(A2)>4, “High”, “Low”)Low
34.1=IF(TRUNC(A3)>4, “High”, “Low”)High
44.9=IF(TRUNC(A4)>4, “High”, “Low”)High

Explanation: Quality scores can sometimes be fractional. Businesses can simplify product grading and ensure consistent quality standards by truncating and evaluating.

#### Example 12: Budget Approval

Purpose: Approve budgets only if the truncated proposed budget exceeds the allocated budget.

ABC
1Allocated BudgetProposed BudgetFormula
2\$10,000\$10,500.75=IF(TRUNC(B2)<=A2, “Approved”, “Denied”)
3\$7,000\$6,999.99=IF(TRUNC(B3)<=A3, “Approved”, “Denied”)
4\$5,000\$5,000.01=IF(TRUNC(B4)<=A4, “Approved”, “Denied”)

Explanation: Budget approvals are crucial for controlling expenses. Businesses can make quick approval decisions by truncating the proposed budget and comparing it with the allocated budget, ensuring financial discipline.

### ðŸ“Œ Part 3: Tips and Tricks

• ðŸŒŸ While TRUNC removes decimals, remember that it doesnâ€™t round numbers. For rounding numbers, consider using the ROUND function.
• ðŸŒŸ If you only want to truncate all numbers to whole numbers, you can skip the num_digits argument entirely.
• ðŸŒŸ TRUNC can also work with negative numbers, making it versatile for various business applications.
• ðŸŒŸ Combining TRUNC with other Excel functions can make your data analysis even more robust!