Skip to main content
Solved

Calculate square root of the sum of several numbers, excluding missing data

  • January 30, 2023
  • 3 replies
  • 65 views
S
Forum|alt.badge.img+4

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?

Best answer by augmented

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

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

    3 replies

    A
    Forum|alt.badge.img+18
    • augmented
    • Inspiring
    • Answer
    • January 30, 2023

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

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

    S
    S
    Forum|alt.badge.img+4
    • seanwhitcomb
    • Author
    • Participating Frequently
    • January 30, 2023

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

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


    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))

     

    Alexey_Gusev
    Forum|alt.badge.img+25

    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))

     


    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)) ))

     

    Terms & ConditionsAccessibility statement