Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Function VLookup | Excel Discussion (Misc queries) | |||
How to create a function using references | Excel Worksheet Functions | |||
Custom Function | Excel Discussion (Misc queries) | |||
Is it possible to create your own custom format? | Excel Discussion (Misc queries) | |||
How do I create my own custom combination charts? | Charts and Charting in Excel |