Related Topics: | ||
When entering functions in a spreadsheet, either manually or using the Function Wizard, keep the following guidelines in mind:
When entering text as an input to a function, you must enclose it in quotation marks. This includes situations where you need to specify the data source – DISTR("Folio1!Data 1") – and situations where you need enter a time or date value in one of the accepted text formats - DAY("22-Aug-2011").
For the data source functions, you must specify which of the available data sources you want to link to for any particular function. You can use either of the following formats:
The source name, enclosed in quotation marks (e.g., "Weibull!Folio1!Data1"). This provides an absolute reference to the data source that will not change. You can use this format in spreadsheets and analysis workbooks.
The associated data source index number (e.g., Default1). This provides a reference that you can easily change on-the-fly. This is useful when creating templates that can be used with any data source. You can use this format only in analysis workbooks and only with one of the data sources that are currently associated with the analysis workbook.
If your regional settings use a comma as the decimal separator, you must use a semicolon to separate function arguments (e.g., =RELIABILITY("Folio1!Data 1";A4)).
If you want to use another cell in the spreadsheet/analysis workbook to provide the input for a function, enter the cell reference with a letter to identify the column and a number to identify the row. The cell references can be relative (e.g., A2) or absolute (e.g. $A$2). For example, if you want to obtain the base-10 logarithm of whatever value is entered into cell B10 of the spreadsheet, the function would be =LOG10(B10). Note that clicking the Function Wizard’s Insert Workbook Reference icon will insert the reference of the cell that is currently selected in the spreadsheet. If you want to insert an absolute reference, press CTRL while you click the icon.
If the function requires you to specify a cell in the data source, you must use numbers to identify both the row and column (e.g., for cell A2 in the source data sheet, enter 2 for the row and 1 for the column). For example:
=DATAENTRY(Default1,2,1) returns the value that was entered into cell A2 in the Weibull++ or ALTA standard folio that is the data source for this function.
=FMATRIX(Default1,2,1) returns the value from the second row in the first column of the Fisher variance/covariance matrix that was calculated for that data sheet.
When using one of the spreadsheet date functions (DAY, DAYS360, MONTH, WEEKDAY and YEAR) to enter a date, you can use one of the following accepted text formats:
Month/Day/Year ("8/22/2011"). For example, DAY("8/22/2011") returns 22 (the day of the date).
Month-Day-Year ("22-Aug-2011"). For example, MONTH("22-Aug-2011") returns 8 (the month of the date).
For dates in the current year, you can use the Day-Month format ("22-AUG"). For example, in 2011 WEEKDAY("22-AUG") returns 2 (the day of the week of Monday, August 22, 2011).
You can also enter the serial number directly. For example, YEAR(40828) returns 2011 (the serial date using the 1900 date system).
You can also use the results of other formulas or functions. In particular, you can use the NOW or TODAY functions to generate values on-the-fly. For example, if the current date is 10/24/2011, then MONTH(TODAY()) returns 10.
You can calculate a date's serial number using either of the following two functions:
The DATE function use the inputs of other cells to calculate a serial number that can be used in other functions. For example, if you have three columns, Year, Month and Date, and you want to use the WEEKDAY function, you could use the DATE function to calculate the serial number and then use the WEEKDAY function, e.g., WEEKDAY(DATE(D1,D2,D3)).
The DATEVALUE function is helpful in cases where a worksheet contains dates in an accepted text format that you want to filter, sort, or format as dates, or use in date calculations, e.g., DATEVALUE(D2&"/"&D3&"/"&D1).
When using one of the spreadsheet time functions (HOUR, MINUTE and SECOND) to enter a time, you can use one of the following valid text formats:
Hour:Minute[:Second] [AM/PM]. For example, HOUR("3:30:30 PM") returns 15 (the hour using the twenty-four hour system).
Month/Day/Year Hour:Minute[:Second] [AM/PM]. For example, MINUTE("8/22/2011 3:25:33 AM") returns 25.
You can also enter the serial number directly. For example, MINUTE(0.78125) returns 45 (as the specified serial number represents 6:45 PM).
You can also use the results of other formulas or functions. In particular, you can use the NOW function to generate values on-the-fly. For example, if the current time is 9:42 AM, then HOUR(NOW()) returns 9.
In cases where you need to perform calculations on times, such as using the MINUTE function to obtain the number of minutes between two events, you must first generate the serial number using either of the following two functions:
The TIME function requires you to enter the Hour, Minute and Second in separate fields. For example, TIME(16,48,10) returns 0.700115741 (this is the serial number that is equivalent to 4:48:10 PM).
The TIMEVALUE function requires you to enter the time as text in one of the accepted text formats given above. For example, TIMEVALUE("5:15:30 PM") returns 0.7190972222.
© 1992-2013. ReliaSoft Corporation. ALL RIGHTS RESERVED.