Posts

Purpose Return value Syntax =WEEKDAY(serial_number,[return_type]) serial_number - The date for which you want to get the day of week. return_type - [optional] A number representing day of week mapping scheme. Default is 1. Using the WEEKDAY function The WEEKDAY function takes a date and returns a number between 1-7 representing the day of the week. The WEEKDAY function takes two arguments : serial_number and return_type . Serial_number should be a valid Excel date in serial number format....

Purpose Return value Syntax =ACOSH(number) number - The number to get the inverse hyperbolic cosine of. Using the ACOSH function The Excel ACOSH function returns the inverse hyperbolic cosine of a number. Given the input 2.0, the function returns the value 1.316957897. =ACOSH(2) // returns 1.316957897 Explanation Given the x -component of a point that lies on the right branch of the unit hyperbola given by the equation x² - y² = 1, the ACOSH function returns the hyperbolic angle formed by the point....

Explanation In Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. This means that January 1, 2050 is the serial number 54,789. If you are calculating a date n days in the future, you can add days directly as in the first two formulas. If you want to count by months, you can use the EDATE function, which returns the same date n months in the future or past....

A column chart is a primary Excel chart type, with data series plotted using vertical columns. Column charts are a good way to show change over time because it’s easy to compare column lengths. Like bar charts, column charts can be used to plot both nominal data and ordinal data , and they can be used instead of a pie chart to plot data with a part-to-whole relationship. Column charts work best where data points are limited (i....

Explanation In this example, the goal is to convert negative numbers in column B to zero and leave positive numbers unchanged. Essentially, we want to force negative numbers to zero. With the MAX function The MAX function provides an elegant solution: =MAX(B5,0) This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values. If the value in B5 is a positive number, MAX will return the number as-is, since positive numbers are always greater than zero....

Explanation Some applications show email addresses together with a “display name”, where the name appears first, followed by the email address enclosed in angle brackets (<>). The goal in this example is to create a format like this based on an existing name and email address. In the worksheet shown, column B contains a name, and column C contains an email address. The formula in column D uses the ampersand character (&) to join the name and email address together:...

Explanation Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the input is rejected with a popup message. In this case, we have previously defined the named range “xlist” as D5:D11. This range holds characters that are not allowed. The formula we are using for data validation is:...

Explanation The Excel TRUNC function does no rounding, it just returns a truncated number. It has an optional second argument (num_digits) to specify precision. When num_digits is not provided, it defaults to zero. In this formula for data validation, we use TRUNC to get the non-decimal part of a percentage, after we multiply the percentage by 100. For example, if a user inputs 15%: =TRUNC(.15*100)=(.15*100) =TRUNC(15)=(15) =15=15 =TRUE If a user enters 15....

Purpose Return value Syntax =DATE(year,month,day) year - Number for year. month - Number for month. day - Number for day. Using the DATE function The DATE function creates a date using individual year, month, and day arguments . Each argument is provided as a number, and the result is a serial number that represents a valid Excel date. Apply a date number format to display the output from the DATE function as a date....

About This Shortcut The delete key on a Mac deletes to the left. About This Shortcut Use function with delete on a Mac to delete characters to the right of the cursor.

About This Shortcut This shortcut displays the function arguments dialog box after a valid function has been typed in the formula bar. On the Mac, it displays the Formula Builder dialog box. About This Shortcut In Windows, this shortcut now displays the Name Manager dialog, where you can click the New button, to create a new name . There is no actual shortcut to create a single new name, except through the ribbon: Alt M + MD...

Purpose Return value Syntax =DMAX(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DMAX function The Excel DMAX function gets the maximum value in a given field from a set of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database....

Purpose Return value Syntax =DMIN(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DMIN function The Excel DMAX function gets the minimum value in a given field from a set of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database....

Purpose Return value Syntax =DOLLARFR(decimal_dollar,fraction) decimal_dollar - Pricing as a normal decimal number. fraction - The denominator in the fractional unit. 8 = 1/8, 16 = 1/16, 32 = 1/32, etc. Using the DOLLARFR function The Excel DOLLARFR function converts a dollar price in a regular decimal number format to a dollar price in a particular fractional notation used for securities where pricing is given to the nearest 1/8, 1/16, 1/32, etc....

Purpose Return value Syntax =FALSE() Using the FALSE function The FALSE function returns the Boolean value FALSE. In other words, the three formulas below based on the IF function are functionally equivalent: =IF(A1>65,"OK",FALSE()) =IF(A1>65,"OK",FALSE) =IF(A1>65,"OK") All three formulas return FALSE if the value in A1 is not greater than 65. The FALSE function is classified as a “compatibility function”, needed only for compatibility with other spreadsheet applications. There is no need to use the FALSE function if you are creating a spreadsheet in Excel....

Explanation This formula relies on the FILTER function to retrieve data based on a logical test built with the COUNTIF function: =FILTER(list1,COUNTIF(list2,list1)) working from the inside out, the COUNTIF function is used to create the actual filter: COUNTIF(list2,list1) Notice we are using list2 as the range argument, and list1 as the criteria argument. In other words, we are asking COUNTIF to count all values in list1 that appear in list2. Because we are giving COUNTIF multiple values for criteria, we get back an array with multiple results:...

Explanation In this example, the goal is to list and count values that are duplicated in a set of data at least n times, where n is provided as a variable in cell D5. All data is in the named range data (B5:B16). In the worksheet shown, the formula used in cell F5 is: =UNIQUE(FILTER(data,COUNTIF(data,data)>=D5)) Working from the inside out, the first step is to count the values in data ....

Purpose Return value Syntax =FIND(find_text,within_text,[start_num]) find_text - The substring to find. within_text - The text to search within. start_num - [optional] The starting position in the text to search. Optional, defaults to 1. Using the FIND function The FIND function returns the position (as a number) of one text string inside another. In the most basic case, you can use FIND to locate the position of a substring in a text string....

Explanation The key to this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format “d”. With a hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in row 4 with the dates in columns B and C. To shade days that are weekends, we are using a formula based on the weekday function ....

Explanation The first ROW function generates an array of 7 numbers like this: {5;6;7;8;9;10;11} The second ROW function generates an array with just one item like this: {5} which is then subtracted from the first array to yield: {0;1;2;3;4;5;6} Finally, 1 is added to get: {1;2;3;4;5;6;7} Generic version with named range With a named range , you can create a more generic version of the formula using the MIN function or the INDEX function....