Named formulas

[squiggler47]

Whats the opinion on here of using named formulas?

I just tried this


=(INDEX(A:A,INT(ROW()/17)+2)-INDEX(A:A,INT(ROW()/17)+1))/16*MOD(ROW()-1,16)+INDEX(A:A,INT(ROW()/17)+1)

by creating 3 named formulas :-

DivRow .....=(INT(ROW()/17)+1)
LookupMin =INDEX($A:$A,DivRow)
ModRow =MOD(ROW()-1,16)

and inserting in the formula

=(INDEX(A:A,DivRow+1)-LookupMin)/16*ModRow+LookupMin

it achieved an average of 13% speedup

Is this a good thing or a bad thing from sheet maintainence and clarity?

[romperstomper]

They're a good way of keeping file size down. As long as you remember that most of the formulas are sheet-specific of course.

[martindwilson]

any spreadsheet can calculate faster than me on a calculator !

[squiggler47]

Reminds me of doing stock control with an older gentleman, who was telling me how the youth of today couldnt do anything without a calculator, I challeneged him to total up the list of numbers we had with a calculator while I did it in my head! After we compared results he proudly said I was wrong, I argued I was right, and we checked the printout, he had miss calculated!!

I then proudly announced that the trouble with older people is they cant use calculators!

I think that little story has a lot of relevance to spreadsheets, because its a spreadsheet (Calculator) a lot of people assume that the results it creates are correct! Infact I have had cases where my spreadsheets have been "Too Correct"!

I used to work for a Double Glazing company who drew their roofs and conservatories, bay windows out on the floor by hand! They got an order for a 4meter quarter circle conservatory and had nowhere to draw it, I calculated the whole thing out in a spreadsheet, checked my calculations over and over! When they got on site and did all the brickwork they came back and complained they where 100mm short at the other end!

More checking of calculations and everything worked out, I then hit on the idea of accuracy, I moved 4 angles by 0.1 degrees and a couple of sides by 1mm, and there was 100mm already, on questioning I found out that they only worked to 0.5 degrees and 1/8" accuracy! Sometimes its better to dumb down your answers in a sheet, but always check the results, better still have someone else do the calculations and make sure you are right!