Thanks:  0
Likes:  0

# Thread: Can I use variables in a formula?

1. 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

2. 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.

3. On 2002-03-15 03:13, Aladin Akyurek wrote:
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.

Why not just =SUM(A2:A65536) ?

4. 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
Microsoft Excel/VBA Training

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

5. 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?

6. On 2002-03-15 03:17, Anonymous wrote:
On 2002-03-15 03:13, Aladin Akyurek wrote:
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.

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.

7. 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?

8. 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

9. 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.

10. 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.

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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•