Can I use variables in a formula?
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Can I use variables in a formula?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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



  3. #3
    Guest

    Default

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

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  7. #7
    Guest

    Default

    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. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Northern Ireland
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  10. #10
    Guest

    Default

      
    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)


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com