This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Calculate square root of the sum of several nu...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
498
1

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 29, 2023 05:45 PM

I want to use this mathematical formula to calculate the "average" diameter at breast height (DBH) of a tree with multiple stems:

DBHavg = SQRT({DBH1}^2 + {DBH2}^2 + {DBH3}^2 + {DBH4}^2)

This is the formula I used:

SQRT(POWER({DBH 1 (cm)}, 2) + POWER({DBH 2 (cm)}, 2) + POWER({DBH 3 (cm)}, 2) + POWER({DBH 4 (cm)}, 2))

This formula returns an error for most records because most trees only have one or two stems. Only trees with numbers in all six fields will correctly calculate a value. How can I change the formula so it calculates the square root of however many DBH values are present for a particular record?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 30, 2023 06:45 AM

Hi Sean. Try wrapping each POWER function with an IF check like....

IF({DBH 1 (cm)}, POWER({DBH 1 (cm)},2),0)

Reply

3 Replies 3

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 30, 2023 06:45 AM

Hi Sean. Try wrapping each POWER function with an IF check like....

IF({DBH 1 (cm)}, POWER({DBH 1 (cm)},2),0)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 30, 2023 01:04 PM

Thank you! That helped a lot. I also wanted to make sure the field stays blank if there are no DBH values entered. This field is in inches, so I also had to convert the final value. Here's the formula I went with:

`IF({DBH 1 (cm)} = BLANK(), BLANK(), (SQRT(SUM((IF({DBH 1 (cm)} != BLANK(), POWER({DBH 1 (cm)},2),0) + IF({DBH 2 (cm)} != BLANK(), POWER({DBH 2 (cm)},2), 0) + IF({DBH 3 (cm)} != BLANK(), POWER({DBH 3 (cm)},2), 0) + IF({DBH 4 (cm)} != BLANK(), POWER({DBH 4 (cm)},2), 0) + IF({DBH 5 (cm)} != BLANK(), POWER({DBH 5 (cm)},2), 0) + IF({DBH 6 (cm)} != BLANK(), POWER({DBH 6 (cm)},2), 0)))) * 0.39))`

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 23, 2023 04:13 AM

Hi,

I think, this clarification might help you later

At first, your formula will stay blank if {DBH 1 (cm)} is blank but other filled. It not checks all fields, just first.

I suppose it's ok, and if there present any data, it starts from first field.

Second, you don't need to use word BLANK (of course you can, but it's not mandatory). Also, you don't need to set 'Value if FALSE' (like in Excel).

For example, you need 'If DBH1 is not empty, show DBH1, and if not, stay empty'. In Excel it's

IF({DBH 1 (cm)} = BLANK(), BLANK(),{DBH 1 (cm)}). In Airtable it's IF({DBH 1 (cm)},{DBH 1 (cm)})

Finaly, it's nice to expand formula editor window and even use Notepad or other editors for better vision while creating formula, and to make it clean, readable and compact. Also, function SUM can simply omit empty values instead of giving 'Error'. So, I would create it this way:

```
IF({DBH 1 (cm)},0.39*SQRT(SUM(
POWER({DBH 1 (cm)},2),
POWER({DBH 2 (cm)},2),
POWER({DBH 3 (cm)},2),
POWER({DBH 4 (cm)},2),
POWER({DBH 5 (cm)},2),
POWER({DBH 6 (cm)},2))
))
```