I hadn’t given much attention to the UNARY_PERCENT function in Google Sheets—until recently.
While creating an EMI calculator as part of my PMT function explanation, I discovered a real-life use case for this function.
If you’d like to explore that spreadsheet, check out my PMT function guide, where I’ve included a link to the calculator.
Is the UNARY_PERCENT Function Important?
In most cases, the UNARY_PERCENT function in Google Sheets isn’t something you’ll frequently need. However, depending on your use case, you may find it useful—especially in financial and percentage-based calculations.
Through real-life examples, I’ll show you where and how you can use this function in Google Sheets.
Purpose of the UNARY_PERCENT Function
The UNARY_PERCENT function in Google Sheets converts a number into a percentage-based decimal value.
Syntax:
UNARY_PERCENT(number)- number – The value to interpret as a percentage.
Examples of the UNARY_PERCENT Function in Google Sheets
Basic Example
=UNARY_PERCENT(20)Result: 0.2
This means 20 is interpreted as 20%, which is 0.2 in calculations.
Now, let’s explore some real-world applications of the UNARY_PERCENT function in Google Sheets.
1. Using UNARY_PERCENT for Percentage-Based Payment Breakdowns
If you receive partial payments based on work completion (e.g., in construction projects), you can use UNARY_PERCENT for easier calculations.
Let’s say you have a payment breakdown for a project:
Formula without UNARY_PERCENT:
=$B$2*D2
This formula calculates the payment based on the percentage in column D.
Formula with UNARY_PERCENT:
=$B$2*UNARY_PERCENT(D2)Now, even if users enter percentages as whole numbers (e.g., 20 instead of 20%), the formula will correctly interpret them as decimal values.
2. Using UNARY_PERCENT for Percentage-Based Growth Calculations
The UNARY_PERCENT function in Google Sheets is useful when working with percentage change calculations, such as population growth.
For example, let’s calculate the US population for 2017, based on the 2016 population and a 0.71% yearly growth rate.
Dataset
- Population in 2016 (Cell A2):
322,179,605 - Yearly % change (Cell B2):
0.71%
Formula (Without UNARY_PERCENT):
=A2*(B2+1)This works when B2 is formatted as a percentage (0.71%).
Formula (With UNARY_PERCENT):
=A2*(UNARY_PERCENT(B2)+1)This is useful when users enter raw numbers instead of percentages (0.71 instead of 0.71%).
3. Using UNARY_PERCENT in Financial Calculations
The UNARY_PERCENT function in Google Sheets is especially useful in financial formulas, such as:
- PMT (Loan Payment Calculation)
- IPMT (Interest Payment Calculation)
- PPMT (Principal Payment Calculation)
Example: Loan EMI Calculation
Dataset
- Loan Amount (Cell B1):
$85,000 - Loan Tenure (Years) (Cell B2):
30 - Interest Rate (Annual) (Cell B3):
4%
Formula Without UNARY_PERCENT:
=PMT(B3/12, B2*12, B1)This formula works when B3 is entered as 4% (formatted as a percentage).
Formula With UNARY_PERCENT:
=PMT(UNARY_PERCENT(B3)/12, B2*12, B1)If B3 contains 4 instead of 4%, this formula will correctly convert it for calculations.
Conclusion
The UNARY_PERCENT function in Google Sheets is a simple yet powerful tool for handling percentage-based calculations. It’s particularly useful when:
- Users enter percentages as whole numbers instead of percentage-formatted values.
- You need cleaner, more consistent formulas for financial and growth-related calculations.
While you may not always need it, understanding how to use UNARY_PERCENT can help simplify your Google Sheets formulas.
