I was not giving much attention to the UNARY_PERCENT function in Google Sheets, until recently.
I have quite recently prepared an EMI calculator, as part of my PMT formula explanation and then I came to know the real-life use of this function.
To lay your hands on a copy of that Spreadsheet, please check my PMT function guide. In that, I have included a link to that specific Sheet.
According to my own experience, there is no need to give much attention to the UNARY_PERCENT function. But you may or may not find it useful in your case.
With my real-life examples, you can understand where you can use this operator type function in Docs Sheets.
Purpose:
The purpose of the UNARY_PERCENT function in Docs Sheets is to return a value interpreted as a percentage.
Syntax:
UNARY_PERCENT(percentage)
percentage – It’s the value to interpret as a percentage.
Examples to the UNARY_PERCENT Function in Google Sheets
Basic Example:
=UNARY_PERCENT(20)
Result: 0.2
What does that result mean?
The result value is 0.2, which means 20%. See the below few examples to understand the real-life use of the UNARY_PERCENT function in Google Sheets.
UNARY_PERCENT Function in Percentage Payment Break-Up
If you are getting a partial payment based on the completion of a job (mostly in construction projects), then you can include this function in your calculation.
See the percentage payment break-up of an activity. The below formula in cell E2 is dragged down.
=$B$2*D2
Now here is the same payment break_up that includes the UNARY_PERCENT function.
=$B$2*unary_percent(D2)
UNARY_PERCENT in Calculations Like Percentage Change in Population
As per this page, the population in the US in 2016 is 322,179,605. The yearly percentage change in population is 0.71%.
With these two input values, we can find the population in the US in 2017. You can use the formula as below in Google Sheets.
Population in 2016 in Cell A2: 322,179,605
Yearly % change in Cell B2: 0.71%
Formula (Population in 2017) in Cell C2:
=A2*(B2+1)
See the same formula below but in a different way.
This time the input values are the same except the value in cell B2, which is as below.
Cell B2 (yearly % change): 0.71
Formula in Cell C2:
=A2*(unary_percent(B2)+1)
The Use of UNARY_PERCENT Function in Financial Calculations in Google Sheets
In financial functions like PMT, IPMT, PPMT etc. you can use the UNARY_PERCENT function in Google Sheets as below.
Home Loan Amount in Cell B1: USD 85,000.00
Tenure (Years) in Cell B2: 30
Interest Rate (% P.A.) in Cell B3: 4%
This formula returns the monthly EMI.
=PMT(B3/12,B2*12,B1)
In your Google Sheets if you let the users to only enter the percentage in number like 4 instead of 4% (you can use data validation to restrict so), then you can use the PMT formula as below.
=PMT(UNARY_PERCENT(B3)/12,B2*12,B1)
See how I have combined the UNARY_PERCENT in this formula.
Hope you could understand how to use the UNARY_PERCENT function in Google Sheets.