Consulting

Results 1 to 19 of 19

Thread: UDF's

  1. #1

    UDF's

    Hi all,

    Trying to set up a couple of UDF's but having problems with the fidgity bits!!

    First one.. Nice easy one to start with!!

    In the spreadsheet the formula is as follows =SUM(A1:A5)/5

    The ?? Is the bit I can't figure out, how do apply the range A1:A5 to i ??


    Function FMA(i)
    ?? 
    FMA = WorksheetFunction.Sum(i) / 5
    End Functio
    n


    And the second one, blimey

    Sharp intake of breath...

    I'm trying to acheive

    RSI=100-100/(1-A/B)
    A refers to the average of up days in a given period.
    B refers to the average of down days in a given period

    So say there are 5 up days in the period which is 10 days.

    The up days are for example 13, 14, 56, 37, 2. These then need to
    be added and stored in A

    Likewise, we have 5 down days eg 40,67, 34,12,4. Again these all
    need to be added to B

    So what needs to happen is for the spreadsheet to recognise when there
    is an upday from the previous day, or when there is a down day compared
    to the previous day, tall those up and include them in the A & B ??

    Hope that makes some kinda sense??

    Thanks in advance for any advice you can offer

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Range("A1:A" & i)
    Not clear on the second one.

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I think you need to post a sample of your layout.

  4. #4
    Hi all,

    On function one I'm not sure this achieves what I'm looking for, probably a poor explination on my behalf! when I enter, for example, =FMA(E2) I would like the function to sum the previous five days then divide by five. It doesn't have to be specific to the Range A or E. Basically whatever Cell I specify as (i) it will sum and divide the previous five days. It's basically a five day moving average calculation, if that helps ?? (Didn't help me!!)

    Copy of sheet to look at and some additional info on the RSI formula!!

    OK. For arguments sake lets say the given period is 10 days.

    Then within that range of data (you will be looking at the closing prices)
    you need (or should I say the spreadsheet) needs to figure out which day is
    an up day or down day. So for instance say you start at cell D$2. In actual
    fact you cant because there is no prior data. So you would have to start at
    $D3. If that cell is a higher value than $D2 that constitutes as up day. If
    it is lower than constitutes a down day.

    Then you move on to the next cell. So would calculate whether $D4 is above
    or below $D3. Then is $D5 above or below $D4. So on and so forth. Then when
    you have got the full 10 days worth of data that means you would have all
    the qualifying data for A and B. That is all the down days added up and all
    the up days added up and then divided by the given period which is 10 in
    this case.

    Thanks again folks!!

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Here are a couple of functions

    Function FMA(i)
    If TypeName(i) = "Range" Then
    If i.Cells.Count = 1 Then
    If i.Row >= 6 Then
    FMA = Application.Average(i.Offset(-4).Resize(5))
    Else
    FMA = ""
    End If
    Else
    FMA = "Formula only applies to single cells"
    End If
    Else
    FMA = "Formula requires a cell reference"
    End If
    End Function 
     
    Function RSI(i)
    If TypeName(i) = "Range" Then
    If i.Cells.Count = 1 Then
    With Application.Caller
    If .Row <= 2 Then
    RSI = ""
    ElseIf i.Value > i.Offset(-1, 0).Value Then
    RSI = "up"
    ElseIf i.Value < i.Offset(-1, 0).Value Then
    RSI = "down"
    Else
    RSI = ""
    End If
    End With
    Else
    RSI "Formula only applies to single cells"
    End If
    Else
    RSI = "Formula requires a cell reference"
    End If
    End Function
    wher you call with =FMA(E2) in row 2, and =RSI(D2) in row 2.

    But you can just use worksheet formulae for both

    FMA: =IF(ROW()<=5,"",AVERAGE(OFFSET(E6,-4,,5)))
    RSI: =IF(ROW(D2)>2,IF(D1>D2,"down",IF(D1<D2,"up","")),"")
    both formulas given as in row 2.

    To get your 10 day figure, just use

    =100-100/(1-COUNTIF(G:G,"up")/COUNTIF(G:G,"down"))

  6. #6
    ~Thanks for the input. I shall take a look over them at the weekend and report back.

    Just tried FMA(i) function on the attached sheet and it doesn't appear to do what I'm looking for, just returns a zero value??

    If you look at the sheet and Column F, with the heading 5DMA, and the formula in there, I'd like to perform that calculation within my own UDF.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by BexleyManor
    Just tried FMA(i) function on the attached sheet and it doesn't appear to do what I'm looking for, just returns a zero value??
    Yeah, that is what I figured. You need to put =FMA(E2) in F2 abd copy down

  8. #8
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Just as a BTW, you could just use your original function passing the range:

    =FMA(A1:A5)
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  9. #9
    Hi all,

    XLD, I tried
    put =FMA(E2) in F2 abd copy down
    just after I'd posted and it worked a treat!! Thank you my friend. I shall now attempt to get my head round your code!!

    Been away for a couple of day's so not contacted my collegue to see if your RSI function returns the correct value. Will check with this tomorrow and get back to you.

    Mark, thanks for the input however this is where my problem lies. It's essential that only five days are selected and my users are basically nuts. If they are left with the responsibility to select the correct range they'll get it wrong. They'll have four days, six days but rarely the required five!! Hence the required single argument (i). I'd like whatever cell (i) pointed to trigger a summing of whatever five cells are above that. Even my users could select a single cell and get that right!! (I hope)

    Thanks guys for your help though, greatly appreciated

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by BexleyManor
    Mark, thanks for the input however this is where my problem lies. It's essential that only five days are selected and my users are basically nuts. If they are left with the responsibility to select the correct range they'll get it wrong. They'll have four days, six days but rarely the required five!! Hence the required single argument (i). I'd like whatever cell (i) pointed to trigger a summing of whatever five cells are above that. Even my users could select a single cell and get that right!! (I hope)
    All ofg my solutions are single cell based as requested.

  11. #11
    Morning All,

    XLD, I was refering to Mark007's comments,
    Just as a BTW, you could just use your original function passing the range =FMA(A1:A5)
    .

    Also, I'm awaiting my collegue to contact me regarding the RSI function. It all looks fantastic and I'm sure it gives the correct result however I'm going to attempt to add to your fine code and see if I can't remove the need to place the "up" and "down" comments on the sheet, instead storing these in variables and using them in the second part of the calculation! Phew, wish me luck!!!

    Thanks for all your help up til now, really appreciated. I'm sure I'll be back shortly !!

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by BexleyManor
    I'm going to attempt to add to your fine code and see if I can't remove the need to place the "up" and "down" comments on the sheet, instead storing these in variables and using them in the second part of the calculation! Phew, wish me luck!!!
    You can do it with a simple worksheet formula

    up:
    =SUMPRODUCT(--(B1:B999<>""),--(B2:B1000<>""),--(B1:B999<B2:B1000))
    or

    down:
    =SUMPRODUCT(--(B1:B999<>""),--(B2:B1000<>""),--(B1:B999>B2:B1000))

  13. #13
    Hi XLD,

    Although another fine suggestion, I want to stay well away from worksheet functions as my users will destroy them within a couple of hours!!

    I'd really like to wrap everything within the RSI(i) UDF giving the user the simplest of input.

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by BexleyManor
    I want to stay well away from worksheet functions as my users will destroy them within a couple of hours.
    Isn't a UDF a worksheet function?

  15. #15
    Yes, I guess!!

    But I'd be hiding all the calculations away in a module away from my numb users so they would only have to enter = RSI(i)

    If I start putting
    =SUMPRODUCT(--(B1:B999<>""),--(B2:B1000<>""),--(B1:B999<B2:B1000))
    in the worksheet then my users will without fail destroy this then attempt to re-write the formula !!

    I'm simply trying to lock away all the calculations from the users for the sake of my sanity!!

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    oops!

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by BexleyManor
    Yes, I guess!!

    But I'd be hiding all the calculations away in a module away from my numb users so they would only have to enter = RSI(i)

    If I start putting
    =SUMPRODUCT(--(B1:B999<>""),--(B2:B1000<>""),--(B1:B999<B2:B1000))
    in the worksheet then my users will without fail destroy this then attempt to re-write the formula !!

    I'm simply trying to lock away all the calculations from the users for the sake of my sanity!!
    You could put the formula in a hidden workbook name, say RSI (now where did I get that from?) and then just have

    =RSI
    not a lot they can mess up with that. By using a hidden name, they cannot see code, they cannot see the name in the names dialog.

    It works I just tried it.

    Sorry to carp on about this but I am strongly opposed to writing code where Excel has better capabilities.

  18. #18
    Sounds like a really good solution but I wouldn't have a clue how to set up something like that??!!

    You say you've tried this? Would it be possible to have a look at your work so I can see how it's done?? It's certainly nothing I'd considered, even knew about, and could open up a whole new world of ideas for me!!

    Thanks for sticking with me on this one, it's been a great help and I truly appreciate your time.

  19. #19
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by BexleyManor
    Sounds like a really good solution but I wouldn't have a clue how to set up something like that??!!

    You say you've tried this? Would it be possible to have a look at your work so I can see how it's done?? It's certainly nothing I'd considered, even knew about, and could open up a whole new world of ideas for me!!

    Thanks for sticking with me on this one, it's been a great help and I truly appreciate your time.
    All I did was goto menu Insert>Name>Define ... and in the 'Names in workbook' box I entered RSI, and in the "Refers to" box I entered the formula.

    If you go back to Insert>Names>Define... you will see RSI there.

    Then I went into the VB IDE, and in the immediate window I type

    activeworkbook.Names("RSI").visible=false

    If you go back to Insert>Names>Define... you will see RSI gone. But you can use it, just type =RSI in a cell.

Posting Permissions

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