Spreadsheets … or not …

There is an interesting thread about “Why You Shouldn’t Use Spreadsheets for Important Work”. See http://science.slashdot.org/story/14/05/27/220202/why-you-shouldnt-use-spreadsheets-for-important-work?utm_source=rss1.0mainlinkanon&utm_medium=feed

I’m not against spreadsheets like I’m not against using a tool to write with, e.g. a word processor.

What I advocate is avoiding spreadsheets whenever possible unless they are “properly done. Spreadsheets start as a blank sheet of paper and your not using the shoulders of giants. The key is full understanding and then implementation of the algorithms and data model, with appropriate testing and verification. This all difficult (costly) with spreadsheets compared to other methods.

I particularly liked the one posting of an actuarial formula found in one spreadsheet. Not untypical:

=IF(F6=”050″,tiers!$D$21+IF(AND(F6=”050″,OR(E6=”W”,E6=”X”)),tiers!$D$29-tiers!$D$26+VLOOKUP(“N”&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&”N/A”&M6/12&”/”&N6&”0-“&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(OR(E6=”W”,E6=”X”),VLOOKUP(“N”&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&”N/A”&M6/12&”/”&N6&”0-“&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”P”,M6=36,N6=60),tiers!$D$29-tiers!$D$26+VLOOKUP(“E”&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6=”P”,M6=36,N6=60),VLOOKUP(“E”&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”P”),tiers!$D$29-tiers!$D$26+VLOOKUP(“E”&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6/12&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6=”P”,VLOOKUP(“E”&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6/12&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”*”,M6=6),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(Q6=48,”0-4″,”6-10″)&M6&”/”&N6&”0-100″&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6=”*”,M6=6),VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6=”PPH08″,L6=”PTH08″),”0-9″,IF(Q6=48,”0-4″,”6-10″))&M6&”/”&N6&IF(R6=125000,”100-125″,”0-100″)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”*”),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6=”PPH08″,L6=”PTH08″),”0-9″,IF(Q6=48,”0-4″,”6-10″))&M6&”/”&N6&IF(AND(OR(L6=”PPH08″,L6=”PTH08″),R6=100000),”0-100″,VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6=”*”,VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6=”PPH08″,L6=”PTH08″),”0-9″,IF(Q6=48,”0-4″,”6-10″))&M6&”/”&N6&IF(AND(OR(L6=”PPH08″,L6=”PTH08″),R6=100000),”0-100″,VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),”ERROR”)))))))))),IF(AND(F6=”050″,OR(E6=”W”,E6=”X”)),tiers!$D$29-tiers!$D$26+VLOOKUP(“N”&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&”N/A”&M6/12&”/”&N6&”0-“&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(OR(E6=”W”,E6=”X”),VLOOKUP(“N”&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&”N/A”&M6/12&”/”&N6&”0-“&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”P”,M6=36,N6=60),tiers!$D$29-tiers!$D$26+VLOOKUP(“E”&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6=”P”,M6=36,N6=60),VLOOKUP(“E”&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”P”),tiers!$D$29-tiers!$D$26+VLOOKUP(“E”&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6/12&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6=”P”,VLOOKUP(“E”&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&”N/A”&M6/12&”/”&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6=”050″,E6=”*”,M6=6),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(Q6=

Advertisements

Comments are closed.

%d bloggers like this: