Posts by Om Avataar

  • Summing Alternate Column Entries in a Row

    Dear Excel Gurus,


    I am seeking an INBUILT combination of functions (meaning NO programming thru' a macro or user-defined function!!) to find the sum of alternate values in a row.


    For example, imagine I have numerical entries in a row, from A5 to U5, and in cell V5 I need to sum just alternate or 1-in-3 entries. That is, for the alternate case, just sum [A5,C5,E5,...], or in the case of 1-in-3, just sum [A5,D5,G5,...]. What formula in cell V5 will help me do this?
    Obviously a simple: =SUM(A5:U5) won't do here. Is there something like: =SUM(A5:U5, 2) or =SUM(A5:U5, 3) which tells Excel to sum from A5:U5 BUT in steps of 2 or 3?


    Please help... thanks in advance. Again, please note that I cannot afford a macro or some user-defined function to accomplish this.


    Thanks.


    - Om

  • Standard Normal Random Number Generator

    Re: Standard Normal Random Number Generator


    Hi... Thanks for the link, but it references only Excel's in-built RAND() and RANK() functions - which facilitate random number generation from a Standard Uniform Distribution, i.e u[0,1].


    I need to generate random numbers from a Standard Normal Distribution, i.e. N(0, 1).


    Hope this clarifies.

  • Range Related

    Re: Range Related


    Thanks to everyone who has guided me in solving my VBA problem referenced in this thread. This thread can now be deemed completely resolved, and therefore closed. Gratzi! :)

  • Range Related

    Dear VBA gurus,


    I need a Range argument ‘s’ unchanged in function ZZ, as ‘s’ is needed for code in a later Module. I thought I would declare a Range variable ‘ss’ within function ZZ itself, increment a specified ‘element’ of ‘ss’, then pass this entire modified Range 'ss' as an argument to yet a different Module. Suffice to say that passing this entire Range 'ss' is a necessary evil...


    Please advise on the code below, where the header of sub-/called function 'workHorse' has been shown for clarity... Thanks a million in advance.


  • Incrementing an Element of a Range Variable: How To

    Re: Incrementing an Element of a Range Variable: How To


    Thanks for your replies, dangelor & Batman... I must unreservedly apologize for trying to oversimplify my actual problem statement in the initial help request.


    See, thing is, I need my original 's' Range unchanged for code in another Module, while modified Range 'ss' itself needs to be passed as an argument to a different Module... Suffice to say that passing the entire modified Range 'ss' is a necessary evil...


    Please advise on the re-posed (and truer to actual) code below, with (just the) header of sub-/called function workHorse shown for clarity... Thanks a million.


  • Incrementing an Element of a Range Variable: How To

    Below is just a test program for me to learn how to manipulate/change individual elements/members of a Range variable in VBA (if possible, that is).


    Please advise... Thanks a million.


    Code
    Public Function Fuse(s As Range, custID As Integer, delta As Double) As Double
    Dim ss As Range
    Set ss = s
        
    ss.Cells(custID, 1).Value = ss.Cells(custID, 1).Value + delta  'Error in this statement; Please advise
    Fuse = ss.Cells(custID, 1).Value
End Function

  • Assigning a 'Range' Argument to a 'Range' Variable

    Re: Assigning a 'Range' Argument to a 'Range' Variable


    Thanks for the quick reply, Norie... I'll try implementing it in my application shortly.


    Thing is, I need my original 's' Range unchanged for code in another Module, while modified Range 'ss' needs to be passed as an argument to a different Module... Suffice to say its a necessary evil.

  • Modifying Elements of a 'Range' Argument

    Dear VBA Gurus,


    I need to pass a Range argument 's' to a caller/main public function ZZ, increment an individual element within 's' by an amount delta within ZZ, and subsequently pass this MODIFIED 's' as a Range argument to another called function 'uncdp', as skeletally presented in code below:


    ===================================


    ====================================


    However, an error is thrown up at the code statement:


    s(ID) = s(ID) + delta


    Why am I getting this error?


    i) Can't an element of a Range argument ('s' in code above) be modified within its own public function (ZZ above)?


    ii) Or will I need to declare via 'Dim ...' a new Range variable, say 'ss', within ZZ, assign 's' to 'ss', increment element 'ss(ID)', and then pass this modified 'ss' Range object as an argument to called public function:
    uncdp(s, r, a, time) ?


    iii) Or is there a more efficient method?


    Please advise with some code corrections/suggestions, where possible.


    Thanks.

  • Getting Size of a 2-D Array

    Say I have:


    Public Function xyz(ss As Range, rr As Range) as Double


    with arguments 'ss' and 'rr' being 5R-by-6C and 6R-by-20C arrays respectively.


    How do I assign each array's #Rows and/or #Cols to integer variables declared within the functions?


    Tried using property 'RowCount' but got an error.


    Please advise... Thanks

  • Variable Scope

    For 2 public functions, one 'caller' & the other 'called', can I have LOCAL variables with the same name in both [as long as I do NOT pass these similarly named LOCAL variables as arguments from 'caller' to 'called' function]?


    Better yet, as in other programming languages, can one declare GLOBAL-scope variables within a 'main' function in Excel VBA so that they may be accessed/manipulated by other sub-/'called' functions without having to pass these Global variables explicitly as arguments to each sub-/'called' function?


    Please advise... Thanks.

  • ReDim for 2-D Arrays

    Please correct me if I'm wrong, but as I understand it, for a 1-D array:


    ReDim V(10) <<--- gives an 11-element array, with individual elements referenced V(0) to V(10)


    while


    ReDim V(1 To 10) <<--- gives a 10-element array, with individual elements referenced V(1) to V(10)(called "Option Base 1" or similar in technical speak, I think)


    ===============


    For a 2-D array,


    ReDim W(10, 5) <<--- gives a 10R-by-5C array with "Option Base 1" the default option, correct? So that the first element of W is referenced W(1, 1)?


    Or do we need to explicitly declare: ReDim W(1 To 10, 1 To 5)?



    Thanks, and apologies if the question sounds silly...

  • Caller/Called Function Location in Project Explorer

    If I have 2 public functions (PF) - one 'ABC' main function which calls another 'xyz' function, where should the two be located in Excel VBA's Project Explorer window?


    1) Both in the same module, i.e. Module1?


    OR


    2) 1) Both in different modules, i.e. 'ABC' in Module1 and 'xyz' in Module2?


    OR


    3) Some other relative location of the 2 PFs



    Please advise... Thanks.

  • Populating a 2-D array with 1-D Columns

    Re: Populating a 2-D array with 1-D Columns


    Someone, anyone, please assist... thanks. Usually, I post a query on this excellent forum and it gets answered by some kind soul - with generic code and all - well under an hour, but this time round...


    :? :(