10 More Custom Google Sheets Formulas You Need to Try

by

Quick Recap: Adding Custom Formulas

To add custom formulas to your Google Sheets, follow these steps:

  1. Open Google Sheets and select Extensions > Apps Script.
  2. Copy and paste the function code into the script editor.
  3. Save the script, and then you can start using your new formulas directly in your Google Sheet.

1. Calculate Percentage

Formula: =PERCENTAGE(value, total)

This formula helps you calculate the percentage of a given value compared to a total.

function PERCENTAGE(value, total) {
  return (value / total) * 100;
}

Usage example: Enter =PERCENTAGE(50, 200) to get 25.

Explanation: The function simply divides the value by the total and multiplies by 100 to find the percentage.

2. Convert Hours to Minutes

Formula: =HOURS_TO_MINUTES(hours)

This function helps convert hours into minutes.

function HOURS_TO_MINUTES(hours) {
  return hours * 60;
}

Usage example: Enter =HOURS_TO_MINUTES(3) to get 180.

Explanation: This function multiplies the given hours by 60 to convert them to minutes. It’s perfect for quick time conversions.

3. Calculate Circle Circumference

Formula: =CIRCUMFERENCE_OF_CIRCLE(radius)

Want to calculate the circumference of a circle? This formula makes it simple.

function CIRCUMFERENCE_OF_CIRCLE(radius) {
  return 2 * Math.PI * radius;
}

Usage example: Enter =CIRCUMFERENCE_OF_CIRCLE(5) to get 31.4159.

Explanation: The formula multiplies the radius by 2 * π to calculate the circumference. It’s a useful formula when working with geometric data.

4. Find Minimum Value

Formula: =FIND_MIN(value1, value2, ...)

This function allows you to find the smallest value from a list of numbers.

function FIND_MIN(...values) {
  return Math.min(...values);
}

Usage example: Enter =FIND_MIN(10, 20, 5, 30) to get 5.

Explanation: Using the Math.min() function, this custom formula returns the smallest value from the list of numbers provided.

5. Calculate Average

Formula: =AVERAGE(value1, value2, ...)

This formula calculates the average of a list of numbers.

function AVERAGE(...values) {
  return values.reduce((a, b) => a + b, 0) / values.length;
}

Usage example: Enter =AVERAGE(10, 20, 30) to get 20.

Explanation: This function adds all the provided values and divides by the total number of values to find the average.

6. Capitalize First Letter of Each Word

Formula: =CAPITALIZE_FIRST_LETTER(text)

This function helps capitalize the first letter of each word in a string.

function CAPITALIZE_FIRST_LETTER(str) {
  return str.replace(/\b\w/g, (char) => char.toUpperCase());
}

Usage example: Enter =CAPITALIZE_FIRST_LETTER("hello world") to get Hello World.

Explanation: The function uses a regular expression to capitalize the first letter of each word, making your text more readable and properly formatted.

7. Reverse a String

Formula: =REVERSE_STRING(text)

Need to reverse a text string? This custom formula does it for you.

function REVERSE_STRING(str) {
  return str.split('').reverse().join('');
}

Usage example: Enter =REVERSE_STRING("Hello") to get olleH.

Explanation: The function splits the string into an array of characters, reverses the array, and then joins it back into a string.

8. Calculate Simple Interest

Formula: =SIMPLE_INTEREST(principal, rate, time)

This formula calculates simple interest based on the principal, rate, and time.

function SIMPLE_INTEREST(principal, rate, time) {
  return (principal * rate * time) / 100;
}

Usage example: Enter =SIMPLE_INTEREST(1000, 5, 2) to get 100.

Explanation: This function calculates simple interest using the formula (Principal * Rate * Time) / 100.

9. Calculate Body Mass Index (BMI)

Formula: =BMI(weight, height)

Calculate BMI given weight (in kg) and height (in meters).

function BMI(weight, height) {
  return weight / (height * height);
}

Usage example: Enter =BMI(70, 1.75) to get 22.86.

Explanation: The formula divides the weight by the square of the height to calculate the BMI, which is useful for health-related data tracking.

10. Convert Inches to Centimeters

Formula: =INCHES_TO_CM(inches)

This function converts inches to centimeters.

function INCHES_TO_CM(inches) {
  return inches * 2.54;
}

Usage example: Enter =INCHES_TO_CM(10) to get 25.4.

Explanation: This function multiplies the given length in inches by 2.54 to convert it to centimeters. It’s helpful for those working with different units of measurement.