SUM() not working on Google script function cell value in Google Sheet

0
votes

I have a google sheet like

enter image description here

The Columns are like
Dimension: C
Calculated Area: D
Total Area: E

The user has to enter Dimensions and the Calculated Area is auto filled by the calculated area using a custom Google Sheet function which returns float number.

=areaInchToFoot(C9)

Total Area field calculates sum of the fields of Calculated Area thus the formula used is

=SUM(D9:D15)

But it gives only 0 in the sum field.

1
excelgoogle-apps-scriptgoogle-sheets
Can you provide the script of areaInchToFoot()? Because I thought that from your image, the values calculated by areaInchToFoot() might be put as the string type. If the values are the string type, the result by SUM() becomes 0. So can I confirm about this? - Tanaike
Sometimes just doing a *1 as the last operation is sufficient ie "=areaInchToFoot(C9)*1" - Solar Mike

1 Answers

0
votes

You can do this with inbuilt functions:

=ARRAYFORMULA(ROUND(CONVERT(INDEX(TRANSPOSE(QUERY(,"Select "&join(",",REGEXREPLACE(A2:A6,"(\d+)""\s+x\s+(\d+)""","$1*$2")))),,2),"in^2","ft^2"),2))
  • REGEX to REPLACE x to * and remove "(114*121)
  • JOIN the array using ,(114*121,46*21,..)
  • QUERY to evaluate each of those strings
  • INDEX/TRANSPOSE to remove query's headers and transpose columns to rows
  • CONVERT to convert inch2 to ft2
  • ROUND it to 2 decimals.
  • SUM up, if needed.