Can I use variables in a formula?

[BarrettM]

I need a formula to sum a column starting in the second row and finishing at row(x).


Row X can change every time the workbook is open so I think I need to use a variable.

Do you have any suggestions.

Thank you

[Aladin Akyurek]

On 2002-03-15 02:07, BarrettM wrote:
I need a formula to sum a column starting in the second row and finishing at row(x).


Row X can change every time the workbook is open so I think I need to use a variable.

Do you have any suggestions.

Thank you

Barrett,

=SUM(OFFSET(A2,0,0,MATCH(9.99999999999999E+307,A:A)-1,1))

should do what you want.

Aladin

On 2002-03-15 03:13, Aladin Akyurek wrote:

Barrett,

=SUM(OFFSET(A2,0,0,MATCH(9.99999999999999E+307,A:A)-1,1))

should do what you want.

Aladin

Aladin
Why not just =SUM(A2:A65536) ?

[Dave Hawley]

I would advise using entire columns a references as it's bad practice and can soon lead to re-calculation slow down.

The ideal method would be to use a Dynamic range, this way you can reference this range in as many formulae as you like without effecting Excels calculations.

I have many examples here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Once you get the general gist of these they can enhance spreadsheets greatly.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-15 03:27 ]

[BarrettM]

I've tried both suggestions but to no avail. I can't just enter a formula to the end as the positioning of the Totals row is like this:
I really feel that a variable is the right way to go but I don't know how to do this or even if it can be done. I'm getting really desperate now!

Qualification Centre 0.80 0.80 0.80
Qualification Centre 0.50 0.50 0.50
Qualification Centre 0.40 0.40 0.40
Qualification Centre 0.80 0.80 0.80
Qualification Centre 5.00 5.00 5.00

TOTALS 66.31 50.65 #NAME?

[Aladin Akyurek]

On 2002-03-15 03:17, Anonymous wrote:

Aladin
Why not just =SUM(A2:A65536) ?

You could also have posed the question:

Why not =SUM(A:A)?

Well, that's also good enough.

I threw OFFSET in, I guess, to convey the idea of applying functions to dynamically computed ranges.

Aladin

On 2002-03-15 03:36, BarrettM wrote:
I've tried both suggestions but to no avail. I can't just enter a formula to the end as the positioning of the Totals row is like this:
I really feel that a variable is the right way to go but I don't know how to do this or even if it can be done. I'm getting really desperate now!

Qualification Centre 0.80 0.80 0.80
Qualification Centre 0.50 0.50 0.50
Qualification Centre 0.40 0.40 0.40
Qualification Centre 0.80 0.80 0.80
Qualification Centre 5.00 5.00 5.00

TOTALS 66.31 50.65 #NAME?

I can't follow any of this.
What are the totals 66.31 and 50.65? What are they the totals of?
What formula is producing #NAME?

Are you trying to enter the total formulas manually or with VBA?

[BarrettM]

Thanks everyone, I eventually got it to work using a variable in the formula, I was just getting the syntax wrong.

Many thanks for everyones help

[Aladin Akyurek]

On 2002-03-15 04:51, BarrettM wrote:
Thanks everyone, I eventually got it to work using a variable in the formula, I was just getting the syntax wrong.

Many thanks for everyones help

Barrett,

I'd appreciate if you could post the previous and modified formula that now uses a variable.

Aladin

On 2002-03-15 03:39, Aladin Akyurek wrote:

You could also have posed the question:

Why not =SUM(A:A)?

Well, that's also good enough.

I threw OFFSET in, I guess, to convey the idea of applying functions to dynamically computed ranges.

Aladin

You said :-
"You could also have posed the question:
Why not =SUM(A:A)?
Well, that's also good enough."


No - I don't think so,

=SUM(A:A) is not the same as =SUM(A2:A65536)