UDF's

[BexleyManor]

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 Function


And the second one, blimey :banghead:

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 :clap:

[Jacob Hilderbrand]

Range("A1:A" & i)

Not clear on the second one.

[mdmackillop]

I think you need to post a sample of your layout.

[BexleyManor]

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!!

[Bob Phillips]

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

[BexleyManor]

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

[Bob Phillips]

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

[mark007]

Just as a BTW, you could just use your original function passing the range:


=FMA(A1:A5)

:)

[BexleyManor]

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

[Bob Phillips]

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.

[BexleyManor]

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 !!

[Bob Phillips]

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

[BexleyManor]

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. :thumb

[Bob Phillips]

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?

[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!! :bug:

[Bob Phillips]

oops!

[Bob Phillips]

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!! :bug:

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.

[BexleyManor]

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

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. :clap:

[Bob Phillips]

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

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. :clap:

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.