Prev: Unique Rank with Duplicate Entries
Next: Lookup function question
From: pbart on 30 Mar 2010 11:42
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.


From: T. Valko on 30 Mar 2010 12:30
>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.

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!

--
Biff
Microsoft Excel MVP


"pbart" <pbart(a)discussions.microsoft.com> wrote in message
news:1E4B1CF5-3604-4968-9A38-E9E728447733(a)microsoft.com...
> 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.
>
>


From: Billns on 30 Mar 2010 16:28
On 3/30/2010 8:42 AM, pbart wrote:
> 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.
>
>
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
 | 
Pages: 1
Prev: Unique Rank with Duplicate Entries
Next: Lookup function question