Named Cell versus Cell Reference
 

If I'm referring to a cell many times in a workbook, is there any difference
between linking to the cell reference (ie $A$3) or naming the cell and
linking to the name? Would it speed up calculations to link to a name?

Thanks

Adam Bush

Named Cell versus Cell Reference
 

Just my guess...

I'd bet any difference in calculation would close to unnoticeable by the human
eye. I think the better reason for doing it is to make updates easier. And
using a nice name can make the formula much more self-documenting, too.


wrote:

If I'm referring to a cell many times in a workbook, is there any difference
between linking to the cell reference (ie $A$3) or naming the cell and
linking to the name? Would it speed up calculations to link to a name?

Thanks

Adam Bush

--

Dave Peterson

Named Cell versus Cell Reference
 

Dave,

How about if I have a very long formula with if statements. Would it speed
up calculations to create a function in VBA rather than putting this long
formula in every cell?

Thanks

Adam Bush

"Dave Peterson" wrote:

Just my guess...

I'd bet any difference in calculation would close to unnoticeable by the human
eye. I think the better reason for doing it is to make updates easier. And
using a nice name can make the formula much more self-documenting, too.


wrote:

If I'm referring to a cell many times in a workbook, is there any difference
between linking to the cell reference (ie $A$3) or naming the cell and
linking to the name? Would it speed up calculations to link to a name?

Thanks

Adam Bush

--

Dave Peterson

Named Cell versus Cell Reference
 

My bet is no.

For the most part, UDFs are much slower than using the functions built into
excel.

You may want to read Charles Williams' notes on calculation:
http://www.decisionmodels.com/calcsecrets.htm

wrote:

Dave,

How about if I have a very long formula with if statements. Would it speed
up calculations to create a function in VBA rather than putting this long
formula in every cell?

Thanks

Adam Bush

"Dave Peterson" wrote:

Just my guess...

I'd bet any difference in calculation would close to unnoticeable by the human
eye. I think the better reason for doing it is to make updates easier. And
using a nice name can make the formula much more self-documenting, too.


wrote:

If I'm referring to a cell many times in a workbook, is there any difference
between linking to the cell reference (ie $A$3) or naming the cell and
linking to the name? Would it speed up calculations to link to a name?

Thanks

Adam Bush

--

Dave Peterson


--

Dave Peterson

Named Cell versus Cell Reference
 

Dave,

But I'm not using a function built into excel, I'm using a formula with six
if statements and several polynomials. Would the UDF improve this?

Thanks

Adam Bush

"Dave Peterson" wrote:

My bet is no.

For the most part, UDFs are much slower than using the functions built into
excel.

You may want to read Charles Williams' notes on calculation:
http://www.decisionmodels.com/calcsecrets.htm

wrote:

Dave,

How about if I have a very long formula with if statements. Would it speed
up calculations to create a function in VBA rather than putting this long
formula in every cell?

Thanks

Adam Bush

"Dave Peterson" wrote:

Just my guess...

I'd bet any difference in calculation would close to unnoticeable by the human
eye. I think the better reason for doing it is to make updates easier. And
using a nice name can make the formula much more self-documenting, too.


wrote:

If I'm referring to a cell many times in a workbook, is there any difference
between linking to the cell reference (ie $A$3) or naming the cell and
linking to the name? Would it speed up calculations to link to a name?

Thanks

Adam Bush

--

Dave Peterson


--

Dave Peterson

Named Cell versus Cell Reference
 

<Would the UDF improve this?

Almost certainly not. It is very hard to get even near the speed of Excels intrinsic calculations, both formulas and functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

" m wrote in message
...
| Dave,
|
| But I'm not using a function built into excel, I'm using a formula with six
| if statements and several polynomials. Would the UDF improve this?
|
| Thanks
|
| Adam Bush
|
| "Dave Peterson" wrote:
|
| My bet is no.
|
| For the most part, UDFs are much slower than using the functions built into
| excel.
|
| You may want to read Charles Williams' notes on calculation:
| http://www.decisionmodels.com/calcsecrets.htm
|
| wrote:
|
| Dave,
|
| How about if I have a very long formula with if statements. Would it speed
| up calculations to create a function in VBA rather than putting this long
| formula in every cell?
|
| Thanks
|
| Adam Bush
|
| "Dave Peterson" wrote:
|
| Just my guess...
|
| I'd bet any difference in calculation would close to unnoticeable by the human
| eye. I think the better reason for doing it is to make updates easier. And
| using a nice name can make the formula much more self-documenting, too.
|
|
| wrote:
|
| If I'm referring to a cell many times in a workbook, is there any difference
| between linking to the cell reference (ie $A$3) or naming the cell and
| linking to the name? Would it speed up calculations to link to a name?
|
| Thanks
|
| Adam Bush
|
| --
|
| Dave Peterson
|
|
| --
|
| Dave Peterson
|

Named Cell versus Cell Reference
 

Niek,

The reason I'm asking this is I have an entire worksheet that does
calculations for a formula that can all be condensed in VBA. This would save
me a pretty large worksheet worth of calculations. Do you think this would
speed up my workbook at all if I got rid of this extra worksheet?

Thanks

Adam Bush

"Niek Otten" wrote:

<Would the UDF improve this?

Almost certainly not. It is very hard to get even near the speed of Excels intrinsic calculations, both formulas and functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

" m wrote in message
...
| Dave,
|
| But I'm not using a function built into excel, I'm using a formula with six
| if statements and several polynomials. Would the UDF improve this?
|
| Thanks
|
| Adam Bush
|
| "Dave Peterson" wrote:
|
| My bet is no.
|
| For the most part, UDFs are much slower than using the functions built into
| excel.
|
| You may want to read Charles Williams' notes on calculation:
| http://www.decisionmodels.com/calcsecrets.htm
|
| wrote:
|
| Dave,
|
| How about if I have a very long formula with if statements. Would it speed
| up calculations to create a function in VBA rather than putting this long
| formula in every cell?
|
| Thanks
|
| Adam Bush
|
| "Dave Peterson" wrote:
|
| Just my guess...
|
| I'd bet any difference in calculation would close to unnoticeable by the human
| eye. I think the better reason for doing it is to make updates easier. And
| using a nice name can make the formula much more self-documenting, too.
|
|
| wrote:
|
| If I'm referring to a cell many times in a workbook, is there any difference
| between linking to the cell reference (ie $A$3) or naming the cell and
| linking to the name? Would it speed up calculations to link to a name?
|
| Thanks
|
| Adam Bush
|
| --
|
| Dave Peterson
|
|
| --
|
| Dave Peterson
|

Named Cell versus Cell Reference
 

Hi Adam,

Most tests show that the number of formulas to be evaluated is more important for performance than the complexity of the formulas
itself. So that makes you think that missing an entire sheets should help.
But then again, UDFs are mostly much slower than comparable formulas.

Why not try? Do let us know your findings!

BTW, if performance is a problem, visit Charles Williams' site for lots of tips and information:

www.decisionmodels.com

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

" m wrote in message
...
| Niek,
|
| The reason I'm asking this is I have an entire worksheet that does
| calculations for a formula that can all be condensed in VBA. This would save
| me a pretty large worksheet worth of calculations. Do you think this would
| speed up my workbook at all if I got rid of this extra worksheet?
|
| Thanks
|
| Adam Bush
|
| "Niek Otten" wrote:
|
| <Would the UDF improve this?
|
| Almost certainly not. It is very hard to get even near the speed of Excels intrinsic calculations, both formulas and
functions.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| " m wrote in message
| ...
| | Dave,
| |
| | But I'm not using a function built into excel, I'm using a formula with six
| | if statements and several polynomials. Would the UDF improve this?
| |
| | Thanks
| |
| | Adam Bush
| |
| | "Dave Peterson" wrote:
| |
| | My bet is no.
| |
| | For the most part, UDFs are much slower than using the functions built into
| | excel.
| |
| | You may want to read Charles Williams' notes on calculation:
| | http://www.decisionmodels.com/calcsecrets.htm
| |
| | wrote:
| |
| | Dave,
| |
| | How about if I have a very long formula with if statements. Would it speed
| | up calculations to create a function in VBA rather than putting this long
| | formula in every cell?
| |
| | Thanks
| |
| | Adam Bush
| |
| | "Dave Peterson" wrote:
| |
| | Just my guess...
| |
| | I'd bet any difference in calculation would close to unnoticeable by the human
| | eye. I think the better reason for doing it is to make updates easier. And
| | using a nice name can make the formula much more self-documenting, too.
| |
| |
| | wrote:
| |
| | If I'm referring to a cell many times in a workbook, is there any difference
| | between linking to the cell reference (ie $A$3) or naming the cell and
| | linking to the name? Would it speed up calculations to link to a name?
| |
| | Thanks
| |
| | Adam Bush
| |
| | --
| |
| | Dave Peterson
| |
| |
| | --
| |
| | Dave Peterson
| |
|
|
|