Using Array Formulas

[Rob]

I am experimenting with Array Formula's.
Can one use names as cell references so that the result formula reads
something like {=worked_hours*fee} instead of {=A3:A7*B2}?

Although I name the range A3:A7 "worked_hours" and create the formula by
dragging the mouse pointer over the range A3:A7, I do not get the range
name in the formula.

Am I looking for something that doen't exsist?

Help appriciated - Rob

 

[Bernard Rey]

I am experimenting with Array Formula's.
Can one use names as cell references so that the result formula reads
something like {=worked_hours*fee} instead of {=A3:A7*B2}?

Although I name the range A3:A7 "worked_hours" and create the formula by
dragging the mouse pointer over the range A3:A7, I do not get the range
name in the formula.

I do get the the range name in the formula. But I get puzzled byu your
question:

- when creating/editing the formula, what difference does (can) it make if
it's an array formula (it only becomes an array formula when validated with
"Command-Enter" - or "Ctrl-Shift-Enter")

- in the example you give, I don't see what can be the need for an array
formula, it just works as it is (but it may just be an example, the real
formula being something different). It would only make sense if you wanted
to combine it with another function. If you have something like this:

{=SUM(Worked_Hours*Fee)}

which should run fine (well, it does here - Excel 2004).

 

[Rob]

Rob wrote:




I do get the the range name in the formula. But I get puzzled byu your
question:

- when creating/editing the formula, what difference does (can) it make if
it's an array formula (it only becomes an array formula when validated with
"Command-Enter" - or "Ctrl-Shift-Enter")

- in the example you give, I don't see what can be the need for an array
formula, it just works as it is (but it may just be an example, the real
formula being something different). It would only make sense if you wanted
to combine it with another function. If you have something like this:

{=SUM(Worked_Hours*Fee)}

which should run fine (well, it does here - Excel 2004).

More details - then.

in A3:A7 the hours I spend on a given task or job, in B2 the fee I get
for each hour I work on the task.

In B3:B7 the fee I get each day.

Range A3:A7 is named "worked_hours"
Range B3:B7 is named "daily_fee"
Cell B2 is named "fee"

Situation #1:

I select cell B3
start formula with"=" [=]
select A3:A7 [=worked_hours]
type "*" [=worked_hours*]
select B2 [=worked_hours*fee]
press ENTER or RETURN

One can see the names used in the formula

Situation #2

I select cell B3:B7
start formula with"=" [=]
select A3:A7 [=A3:A7]
type "*" [=A3:A7*]
select B2 [=A3:A7*B2]
press COMMAND+RETURN
{=A3:A7*B2}

Names are not reflected in formula.

You are right. It is only an example. But, working with other people
learning Excel, I noticed that creating a (working) formula is one thing
and reading back (e.g. next week) what you have done - is another thing.

Reading "=sales*profit_margin" makes more sense than "=D23*Sheet2!H7" -
don't you agree?

I also found that, after applying meaningful names to cells or ranges,
creating formulas is easier to novice Excel users as the names confirm
what the user is doing. If it doesn't say "=sales*profit_margin" in the
formula bar, you know something is going wrong.

Stretching the use of names beyond the functional use in formulas to
create readable formulas. For some this is a great help.

Thanks so far, Bernard - merci!

Rob

BTW Excel 2004 on OS X 10.3.7

 

[Bernard Rey]

in A3:A7 the hours I spend on a given task or job, in B2 the fee I get
for each hour I work on the task.

In B3:B7 the fee I get each day.

Range A3:A7 is named "worked_hours"
Range B3:B7 is named "daily_fee"
Cell B2 is named "fee"

Situation #1:

I select cell B3
start formula with"=" [=]
select A3:A7 [=worked_hours]
type "*" [=worked_hours*]
select B2 [=worked_hours*fee]
press ENTER or RETURN

One can see the names used in the formula
Yes.

Situation #2

I select cell B3:B7
start formula with"=" [=]
select A3:A7 [=A3:A7]
type "*" [=A3:A7*]
select B2 [=A3:A7*B2]
press COMMAND+RETURN
{=A3:A7*B2}

Names are not reflected in formula.

Right. But if you TYPE "=Worked_hours * Fee" instead of selecting the cells
it will run. Well, I agree, it's a bit more "manual"... I understand it
would be easier, of course, as it would be more consistent with the way it
usually behaves.

In fact, there's no need to do it the way you do. Simply enter the formula
(selecting the cells) in B3 the usual way. And then copy it down to B7 : it
will run as expected even if you didn't validate it as an array formula:

Select cell B3
start formula with"=" [=]
select A3:A7 [=worked_hours]
type "*" [=worked_hours*]
select B2 [=worked_hours*fee]
press RETURN
drag the little square on the bottom-right end of cell B3 down to B7
(or do it the old copy-paste way - or CTRL-D - if you prefer)
here you are...

You are right. It is only an example. But, working with other people
learning Excel, I noticed that creating a (working) formula is one thing
and reading back (e.g. next week) what you have done - is another thing.

Reading "=sales*profit_margin" makes more sense than "=D23*Sheet2!H7" -
don't you agree?

Certainly. And I do use names quite often for that very reason. But I don't
use the array formulas the way you describe. I think it was necessary to do
so in earlier versions of Excel, and I remember having used it, but it is no
more (under these circumstances) it seems. Or maybe I didn't come across a
situation where it is needed...

I also found that, after applying meaningful names to cells or ranges,
creating formulas is easier to novice Excel users as the names confirm
what the user is doing. If it doesn't say "=sales*profit_margin" in the
formula bar, you know something is going wrong.

Right too.

 

[Rob]

Rob wrote:

snip

Names are not reflected in formula.

Right. But if you TYPE "=Worked_hours * Fee" instead of selecting the cells
it will run. Well, I agree, it's a bit more "manual"... I understand it
would be easier, of course, as it would be more consistent with the way it
usually behaves.

In fact, there's no need to do it the way you do. Simply enter the formula
(selecting the cells) in B3 the usual way. And then copy it down to B7 : it
will run as expected even if you didn't validate it as an array formula:

Select cell B3
start formula with"=" [=]
select A3:A7 [=worked_hours]
type "*" [=worked_hours*]
select B2 [=worked_hours*fee]
press RETURN
drag the little square on the bottom-right end of cell B3 down to B7
(or do it the old copy-paste way - or CTRL-D - if you prefer)
here you are...

You are right. It is only an example. But, working with other people
learning Excel, I noticed that creating a (working) formula is one thing
and reading back (e.g. next week) what you have done - is another thing.

Reading "=sales*profit_margin" makes more sense than "=D23*Sheet2!H7" -
don't you agree?

Certainly. And I do use names quite often for that very reason. But I don't
use the array formulas the way you describe. I think it was necessary to do
so in earlier versions of Excel, and I remember having used it, but it is no
more (under these circumstances) it seems. Or maybe I didn't come across a
situation where it is needed...

I also found that, after applying meaningful names to cells or ranges,
creating formulas is easier to novice Excel users as the names confirm
what the user is doing. If it doesn't say "=sales*profit_margin" in the
formula bar, you know something is going wrong.

Right too.

Encore merci, Bernard. *I* know how to use the fill handle ;-) but I am
am experimenting with useful (huh ... ) enhancements when it comes to
creating formulas in Excel. I myself would have used "=A3*$B$2" in B3
and copied that down to B7. But - wow - that is intimidating to novice
users.

By using an array formula and names in my example, clicking on any cell
in the range from B3 to B7 should have given "=worked_hours*fee".
Obviously, it is not this simple and it takes an extra step to accomplish.

Let's close the subject, it's dinner time on this side of the ocean.

Rob