Tuesday, May 7, 2013

yogi_SetUp Computed Column For Cell Phone Charges Based On Multiple Criteria


                                          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:


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