Wednesday, March 7, 2012

yogi_Compute Entity Per Specification In Column(s) Row By Row

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #459          www.energyefficientbuild.com

user celsius said:
arrayformula correct syntax?
=ArrayFormula(if(len(I2:I);(QUOTIENT(J2:J,3)*25)+(MOD(I2:I,3)*10);iferror(1/0)))
1. Is this correct syntax? including parentheses (It is not working).
2.I believe this is checking to see if there is a valid number value in I2 (& then proceed), but I need to check for a valid number value in both
I2 and J2.
4. Also, where do I place my title for the column as in something like
IF(ROW(A:A)=1;ʺPriceʺ;
Thank you again.
UPDATE
Thank you Yogi!
Your formula worked
=ArrayFormula(if(row(YY:YY)=1,"Price",if(len(I:I);(QUOTIENT(I:I,3)*25)+(MOD(I:I,3)*10);iferror(1/0))))
How do I add in a summing of the same algorithm for column J (in addition to column I).
Maybe - can one sum formulas, or do we another + with brackets?
=ArrayFormula(if(row(YY:YY)=1,"Price",if(len(I:I);(sum(QUOTIENT(I:I,3)*25)+(MOD(I:I,3)*10):(QUOTIENT(J:J,3)*25)+(MOD(J:J,3)*10));iferror(1/0))))
---------------------------------------------
following is a solution to the problem: