Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
andyiain
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernard Liengme
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
andyiain
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Function VLookup Martin Excel Discussion (Misc queries) 1 March 15th 06 09:32 PM
How to create a function using references Freeman Excel Worksheet Functions 1 January 21st 06 09:32 PM
Custom Function SPeterson Excel Discussion (Misc queries) 3 December 21st 05 08:12 PM
Is it possible to create your own custom format? Scott Steiner Excel Discussion (Misc queries) 5 November 22nd 05 06:46 PM
How do I create my own custom combination charts? Vivi63 Charts and Charting in Excel 2 May 20th 05 10:26 PM


All times are GMT +1. The time now is 07:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"