Programming style with worksheet functions - discussion invited

P

pbart

As a self taught Excel user I appear to have developed a personal style that
differs from most examples of spreadsheets that I see.

One issue is the use of named ranges. I observe that I rarely use
expressions such as
= L9 * "Master sheet"!$F$15 / 100 ,
prefering instead to name all variable and arrays
= Sales * VATrate / 100.

Would professional users regard this as good practice or are there drawbacks?

To take this further, I tend to use array formulae wherever appropriate.
Again I find
{=spectrum*displacement^2} ,
in every row, far more readable (and hence less prone to error) than
="standard spectra"!$B13 * $C15^2
with the usual variation row by row.

Here I can see a downside though. Whilst array formulae do discourage end
users from tampering with the workbook, they are a pain to resize to
incorporate more invoice records or increased frequency ranges.

Again I would be interested in the opinion of others.
 
T

T. Valko

One issue is the use of named ranges.
I observe that I rarely use expressions such as
= L9 * "Master sheet"!$F$15 / 100 ,
prefering instead to name all variable and arrays
= Sales * VATrate / 100.
Click to expand...

This is just a personal preference...

If I have to audit your file it's going to take me twice as long (job
security!!!) because the first thing I have to do is look for all the named
ranges!

If I see a formula like this:

= L9 * "Master sheet"!$F$15 / 100

I instantly know where the referenced data is located.

If I see a formula like this:

= Sales * VATrate / 100

Well, I have to go look for it!

I rarely use named ranges. Ususally, only when the name refers to another
sheet and/or the range is referenced in a long complex formula.

Just my opinoin...

Array formulas are your friend!

In most forums like this one, too many people seem to discourage the use of
arrays by "scaring" the user. Some things can only be done with arrays
(unless you want to clutter up your file with heaps of helper cells). As a
general rule, it depends on the size of the file, the number of calculations
and how long it takes those calculations to execute as to how freely you
should use arrays.

Some folks may think that a file that takes 20 minutes to calculate is
outrageous. Well, it depends on what the file is doing!
 
B

Billns

As a self taught Excel user I appear to have developed a personal style that
differs from most examples of spreadsheets that I see.

One issue is the use of named ranges. I observe that I rarely use
expressions such as
= L9 * "Master sheet"!$F$15 / 100 ,
prefering instead to name all variable and arrays
= Sales * VATrate / 100.

Would professional users regard this as good practice or are there drawbacks?

To take this further, I tend to use array formulae wherever appropriate.
Again I find
{=spectrum*displacement^2} ,
in every row, far more readable (and hence less prone to error) than
="standard spectra"!$B13 * $C15^2
with the usual variation row by row.

Here I can see a downside though. Whilst array formulae do discourage end
users from tampering with the workbook, they are a pain to resize to
incorporate more invoice records or increased frequency ranges.

Again I would be interested in the opinion of others.
Click to expand...
I like your approach -- it makes the formulas much more understandable.
Javelin, an early spreadsheet or perhaps more properly described as an
analysis tool, took this approach. The program, though, was initially
overpriced and did not gain many adherents in the days of Lotus 1-2-3.

Biff does have a point, though. It will take longer for someone else to
audit your file.

Bill
 
P

pbart

Biff, Bill

Something of a mixed vote on the value of named ranges then. The audit
issue is one that I had not really considered beyond 'clear to understand is
good'. Although I am reasonably well aware of software quality issues
applied to coding, I was under the impression that spreadsheets tend to 'slip
under the radar' as far as quality is concerned. Apparently this is not
always the case, otherwise your tongue in cheek reference to 'job security'
would not apply.

In answering one question others seem to be raised such as the idea of
'cluttering up your file with heaps of helper cells'. This too is something
I do routinely, taking the view that a series of steps one could explain to a
10 year old are better than commiting ones life's work to one 'master formula
of immense ingenuity'.

Then I would really mess it up as far as audit is concerned by hiding all
the helper cells along with any intermediate processing worksheets, removing
all gridlines and cell boundaries other than those inviting user input and
finally semi locking the whole thing down with protection - usually <blank>
password though. Seems I would either be the perfect client or a complete
nightmare according to your work objectives on the day!

Once more, thanks for your comments .

Peter
 
R

Roger Govier

Hi Peter

I know Biff has an absolute abhorrence of named Ranges <bg>!!!

I would agree that it can be taken too far, with naming of lots of
individual cells, but a few like VAT, TAX etc are absolutely fine.
Again, putting VAT in a cell on a sheet, and then naming the cell, makes
it easily visible for audit, as opposed to placing the value in the
Refers to part of a Name, and saves having to go through lots of Names
to see the values.

But named ranges, especially Dynamic ranges, in my opinion are quite
different, and I use them extensively for many reasons.

If you make the range name, the same as the column heading, then nothing
can be clearer (in my mind) than
Invoice Date <= xxxx
Sum(Sales Amount)
Sum(Net Amount)
etc.

For any individual named values, like VAT and TAX etc., I put these all
on one sheet (hidden), with clear names alongside the named cells
containing the values.
On the same sheet, I do Insert>Name>Paste>Paste List so that all of the
named ranges can be clearly seen with their name and reference. This
list can be annotated where necessary.

I use the same convention for all ranges
I always have a named range called lr (Last Row), which is then used in
all ranges
lr=COUNTA($A:$A)
Column A, invariably is a column that will always be populated, hence
that is what I inevitably use, but it could be based upon any other
column if required.

Then,the following might be the case
Invoice date=$A$2:INDEX($A:$A,lr)
Sales Amount=$G$2:INDEX($G:$G,lr)
and so on

By using a common counter, you ensure that all ranges are of equal
length. The formulae, are short and easy and don't use the volatile
Offset function.

It takes but a minute to scan ones eye down the list to see that it is
both consistent and accurate, and from an audit point of view, having
satisfied yourself that one range is calculated correctly, then all of
the others will behave the same.

When it comes to VBA, then referring to named ranges, rather than hard
coding ranges, makes it much easier to maintain the code.

Even using Named formulae, can be very beneficial and can make use of
the valuable Evaluate function that is not otherwise available to
worksheet formulae, but is available in VBA. Provided that the name and
formula are in the list on the hidden sheet as mentioned earlier,along
with a description of what it does, then any auditor can see and test
the formula once, and know that every occurrence within the worksheet
will behave the same.

No, Peter, you stick with your use of named ranges and providing you
follow simple logical guidelines, any auditor "worth his salt" should
have a far easier time in auditing your work, not a more difficult
experience.

I have some simple code that will create a list of dynamic ranges very
easily. It can be viewed on Debra Dalgleish's site
http://www.contextures.com/excelfilesRoger.html
and choose
Names -- Create Dynamic Ranges With a Macro
http://www.contextures.com/xlNames03.html
 
P

pbart

Hi Roger

Thank you for the post; sorry it has taken me so long to respond. Many of
the issues you discuss are at the edge of my 'comfort zone' or slightly
beyond, so it has taken me a while to read and experiment. Debra Dalgleish's
site was a bit of an eye opener for me.

Even the expression
'=SUMPRODUCT(--($B$2:$B$50=$C8),--($D$2:$D$50=D$20),$G$2:$G$50)
on your spreadsheet helped. I had seen such expressions used on the forum
but had always found them a particularly opaque piece of programming. With
your explanation, I can at least think of them as a generalised SUMIF and
understand the effect of the double hyphen.

Now that I realise that the index expression can be used within the form
defining ranges, I follow the creation of dynamic ranges. How they are used
to good effect (in charts and pivot tables?) will take somewhat longer to
appreciate. I also need to check out how changing the definition of a named
range impacts array formulae based upon the range - does it bypass the
dreaded "you cannot change part of an array" error.

The use of VBA as 'metacode' (by that I am thinking of a program that writes
the code that is then used to process user data - perhape non-standard
terminology) to create the dynamic arrays was also of interest; something I
will bear in mind. Talking of VBA, I had experienced problems with modifying
an Excel sheet that provided the data input front end to a web service. I
was adding drawing elements (shapes) that changed with the data input to
provide feedback to the user. Every time I modified the layout of the sheet
to make room for the drawing I was back to square one revising all of the
I/O. I pushed hard for the use of named ranges in that instance!

In view of the discussion of auditing, it is something of an irony that the
only time I have used ranges absolutely systematically was when developing an
18 sheet workbook subject to formal software quality requirements, albeit
low-level non-safety-critical. The export of named ranges using VBA, first
variables then arrays, for each worksheet provided the framework for much of
the program documentation. I did not realise it is possible to achieve the
same thing with a worksheet function though; you quote
Insert>Name>Paste>Paste List (Office 97-2003 I think?).

The paragraph in your post that I still haven't got to grips with is "even
using Named formulae can be very beneficial and can make use of the valuable
Evaluate function that is not otherwise available to worksheet formulae, but
is available in VBA". I assume the evaluation is done by a piece of VBA in
response to some event but how and, more so, why the formula is written
eludes me.

Thank you all for the replies. It was worth the risk of exposing my
ignorance in order to provoke such interesting responses.

Regards
Peter
 
Top