Google Spreadsheet Post #1174
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 07, 2013
user Daniel Grizer (http://productforums.google.com/forum/?zx=f30enu3qruz8#!category-topic/docs/spreadsheets/_Eq35HP1Pig)
Problem with arrayformula on form spreadsheet
I want to add a formula to calculate payments for a form. I looked through the forums, and found that I had to use ARRAYFORMULA to extend the formula to the other rows, and I adjusted the formula to add the cells in the column (W2 became W2:W) This has caused the formulas to produce incorrect results. I use a lot of logic functions and greater than and less then operators, could this be the problem?
I copied the spreadsheet to a new document to share here:
https://docs.google.com/spreadsheet/ccc?key=0Aii2ny3PtGC8dDBWaVFtcjFIaENqM2J2TjcxTjRmX1E&usp=sharing
Here is an example of the original formula:
=39.98+8.18+20+sum(I2:K2)+if(or( H2<175,(H2+L2+P2+T2)<700),0,( E2-X2)*(H2-175)/(if(H2<175,0, H2-175)+if(L2<175,0,L2-175)+if( P2<175,0,P2-175)+if(T2<175,0, T2-175)))
And here is how I changed it to an array formula:
=arrayformula(39.98+8.18+20+I2:I+J2:J+K2:K+if(or(H2:H<175,(H2:H+L2:L+P2:P+T2:T)<700),0,(E2:E-X2:X)*(H2:H-175)/(if(H2:H<175,0,H2:H-175)+if(L2:L<175,0,L2:L-175)+if(P2:P<175,0,P2:P-175)+if(T2:T<175,0,T2:T-175))))
Thanks in advance for any help.
-----------------------------------------------------------------------------------------------------------------
following is a solution to the problem
No comments:
Post a Comment