Complex Calculations

Below are some examples of calculations shared by the Adalo Community.

1. Rounding a Number to Nearest Hundredths Using *100/100

If you want to round a number to two decimal places manually, you can multiply it by 100, round it, and then divide by 100 again. For example, to round the number 12.34567 to two decimal places:

ROUND(12.34567 * 100) / 100

This calculation works by:

• Multiplying 12.34567 by 100 → 1234.567

• Rounding it to the nearest integer → 1235

• Dividing by 100 to get it back to the original scale → 12.35

---

2. Calculating a Percentage Increase/Decrease

Let’s say you have a property for last year’s revenue and another for this year’s revenue, and you want to calculate the percentage increase:

((This_Year_Revenue - Last_Year_Revenue) / Last_Year_Revenue) * 100

This formula will:

• Subtract last year’s revenue from this year’s revenue to get the difference.

• Divide that difference by last year’s revenue to calculate the change as a decimal.

• Multiply by 100 to convert the result to a percentage.

---

3. Compound Interest Formula

To calculate compound interest, where you have a principal amount, an interest rate, and a number of periods (e.g., years), you can use the formula:

Principal * (1 + Rate / 100)^Periods

For example, if you invest $1,000 at an annual interest rate of 5% for 3 years, the formula would look like this:

1000 * (1 + 5 / 100)^3

This will calculate the total value after interest is compounded over 3 years.

---

4. Combining Rounding with Other Functions

You might want to round a calculated percentage to one decimal place. For example, if you’re calculating the percentage completion of a task and want to round it:

ROUND((Completed_Tasks / Total_Tasks) * 100, 1)

This formula:

• Divides the number of completed tasks by the total tasks to get the completion rate as a decimal.

• Multiplies by 100 to convert it into a percentage.

• Rounds the result to one decimal place.

---

5. Adjusting Time Based on User Hours

If you want to adjust the current date by a partial day based on the user’s hours, you can use this formula:

Current_Date + ((24 - User_Hours) / 24)

This formula works by:

• Subtracting User_Hours from 24 to get the remaining hours in the day.

• Dividing by 24 to convert the remaining hours into a fraction of a day.

• Adding that fraction of a day to the Current_Date to adjust the time.

---

6. Simple Percentage Calculation

If you want to calculate what percentage a number (Part) is of a total (Total):

(Part / Total) * 100

For example, if you completed 30 tasks out of 50, the formula would be:

(30 / 50) * 100 → 60%

---

7. Adding Days to a Date

To calculate a new date by adding a certain number of days to the current date, you can use:

Current_Date + Days

For example, to add 7 days to today’s date:

Current_Date + 7

This will give you the date that’s 7 days from now.

---

8. Calculating Days between Dates

To calculate the number of days between 2 dates, you can use:

INT(End_Date - Start_Date/Time) or 
INT(End_Date/Time - Start_Date/Time) or 
End_date - Start_Date

Example 2: Date and Time

End_Date = 2025-02-01 15:45
Start_Date = 2025-01-23 10:30
End_Date - Start_Date = 9.21875 days (9 full days and ~5.25 hours)
INT(9.21875) = 9
Result: 9 days(truncated, ignoring partial day)

Example 1: Date Only

End_Date = 2025-02-01
Start_Date = 2025-01-23
End_Date - Start_Date = 9
INT(9) = 9
Result: 9 days

This formula

• Calculates the difference between two date or date-time values. The result is typically in days as a decimal, where the whole number part represents full days and the fractional part represents the time difference.

• Applies the INT function truncates (rounds down) the decimal result to the nearest lower whole number. For example:

  • If the difference is 2.75 days, INT will give 2 days.

  • If the difference is -1.25 days, INT will give -2 days.

This formula:

Key Limitation:

This approach discards fractional parts of a day, so it's not suitable if you need precise time differences (e.g., 9.22 days). Use alternative methods like ROUND or DATEDIF for more nuanced calculations.

---

9. Calculating a Discount

If you want to calculate the discounted price of an item, you can subtract the discount from the original price:

Original_Price - (Original_Price * Discount_Percentage / 100)

For example, if an item costs $100 and there is a 20% discount, the formula would be:

100 - (100 * 20 / 100) → $80