Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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:
Solved! Go to Solution.
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)
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)
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))
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))
))