Can I use variables in a formula?

BarrettM

Board Regular
Joined
Mar 13, 2002
Messages
113
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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.

Aladin


Aladin
Why not just =SUM(A2:A65536) ?
 
Upvote 0
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-15 03:27
 
Upvote 0
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?
 
Upvote 0
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.

Aladin


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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top