View Full Version : UDF's

BexleyManor

05-26-2005, 05:36 PM

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

05-26-2005, 06:17 PM

Range("A1:A" & i)

Not clear on the second one.

mdmackillop

05-27-2005, 12:40 AM

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

BexleyManor

05-27-2005, 02:26 AM

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

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

05-27-2005, 08:06 AM

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

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

05-28-2005, 02:52 AM

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

=FMA(A1:A5)

:)

BexleyManor

05-31-2005, 04:21 PM

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

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

06-01-2005, 02:03 AM

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

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

06-01-2005, 02:52 AM

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

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

06-01-2005, 03:58 AM

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:

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

06-01-2005, 05:24 AM

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:

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 eth 'Names in workbook' box I entered RSI, and in the Refersto 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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.