Wednesday, December 19, 2012

yogi_Create Computed Columns For Product And Cost From Bike Types And Cost LookUp Table


                                          Google Spreadsheet   Post  #930
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 19, 2012
user RAYBEN said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/cOMomDsxjcA)
Help with ARRAY formula to replace complex CHOOSE formula 
Hello,

I need some help replacing a very long CHOOSE formula with an ARRAY formula.  The Choose formula has reached capacity and won't work anymore.  I have replicated the essence of my spreadsheet in the following spreadsheet but mine is much larger and therefore has more selections and google docs cannot handle it anymore.  

https://docs.google.com/spreadsheet/ccc?key=0Ag2nfc8LjckwdFVCTXI4ZUZfUzhWd2RhR29Ta0g3c3c#gid=0

Essentially, the formula looks at multiple columns (types of bikes) to determine which type has been populated and what product has been sold.  From the product it would then determine the cost of the product.
The formula currently being used is as follows: =CHOOSE(IF(A2="A",1,IF(A2="B",2,IF(A2="C",3,IF(B2="D",4,IF(B2="E",5,IF(B2="F",6, IF(C2="G", 7, IF(C2="H", 8, IF(C2="I",9,0))))))))),10,10,10,40,50,60,70,80,90,0)
I have many more bike types and many more products under each bike type and therefore google.doc is having difficulty.

Can someone suggest another formula that might be able handle this function better and won't be limited like the choose function?  I've been trying ARRAY formulas but don't understand it enough to come up with a solution.

Any suggestions would be very much appreciated.

-----------------------------------------------------------------------------------------------
following is a solution to the problem


No comments:

Post a Comment