Create custom function
 

Hi,

I use the following formula all the time: =IF(H2="Saturday",
"Saturday", IF(H2="Sunday", "Sunday", IF(K20.79, "Evening",
"Daytime"))), where the H column has the day of the week and the K
column the time.

Each time I have a new speadsheet I type this in from scratch and copy
it down for as many rows as I have (generally a few thosand), not a big
deal I'll grant you.

I was wondering would it be 'better' to make a custom function that
performs this task and if so how should one go about this. I
understand the concepts around creating a custom function but it's all
very new.

Any help would be appreicated.

Andy.


--
andyiain
------------------------------------------------------------------------
andyiain's Profile: http://www.excelforum.com/member.php...fo&userid=8335
View this thread: http://www.excelforum.com/showthread...hreadid=528528

Create custom function
 

This will do what you want

Function myform(myday, myvalue)
If myday = "Saturday" Or myday = "Sunday" Then
myform = myday
Exit Function
End If
If myvalue 0.79 Then
myform = "Evening"
Else
myform = "Daytime"
End If
End Function

To call it use =myform(H2,K2)
If you want to use it in many workbooks, save it in Personal.XLS and call
with =Personal!myform(H2,K2)
Remember that UDF can be a lot slower than build-in functions
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"andyiain" wrote in
message ...

Hi,

I use the following formula all the time: =IF(H2="Saturday",
"Saturday", IF(H2="Sunday", "Sunday", IF(K20.79, "Evening",
"Daytime"))), where the H column has the day of the week and the K
column the time.

Each time I have a new speadsheet I type this in from scratch and copy
it down for as many rows as I have (generally a few thosand), not a big
deal I'll grant you.

I was wondering would it be 'better' to make a custom function that
performs this task and if so how should one go about this. I
understand the concepts around creating a custom function but it's all
very new.

Any help would be appreicated.

Andy.


--
andyiain
------------------------------------------------------------------------
andyiain's Profile:
http://www.excelforum.com/member.php...fo&userid=8335
View this thread: http://www.excelforum.com/showthread...hreadid=528528

Create custom function
 

Go to the VBIDE (Alt-F11)

Insert a code module (InsertModule)

Type in

Public Function MyFunc(DayRange As Range, TimeRange As Range)
If DayRange.Count 1 Or TimeRange.Count 1 Then
MyFunc = CVErr(xlErrRef)
Else
If DayRange.Value = "Saturday" Or DayRange.Value = "Sunday" Then
MyFunc = DayRange.Value
ElseIf TimeRange.Value 0.79 Then
MyFunc = "Evening"
Else
MyFunc = "Daytime"
End If
End If
End Function


and use it in the worksheet like so

=MyFunc(H2,K2)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"andyiain" wrote in
message ...

Hi,

I use the following formula all the time: =IF(H2="Saturday",
"Saturday", IF(H2="Sunday", "Sunday", IF(K20.79, "Evening",
"Daytime"))), where the H column has the day of the week and the K
column the time.

Each time I have a new speadsheet I type this in from scratch and copy
it down for as many rows as I have (generally a few thosand), not a big
deal I'll grant you.

I was wondering would it be 'better' to make a custom function that
performs this task and if so how should one go about this. I
understand the concepts around creating a custom function but it's all
very new.

Any help would be appreicated.

Andy.


--
andyiain
------------------------------------------------------------------------
andyiain's Profile:
http://www.excelforum.com/member.php...fo&userid=8335
View this thread: http://www.excelforum.com/showthread...hreadid=528528

Create custom function
 

Hi Bob,

That worked a treat, thank you kindly.

Andy


--
andyiain
------------------------------------------------------------------------
andyiain's Profile: http://www.excelforum.com/member.php...fo&userid=8335
View this thread: http://www.excelforum.com/showthread...hreadid=528528