If the number in column B is greater than zero, IF returns 1, otherwise IF returns zero. With the IF function One way to handle this problem is with the IF function. In the example shown, the formula in D5, copied down, is: The logic is simple: when the number B5 is greater than zero, IF returns 1, otherwise IF returns 0. The logical test inside IF can be adjusted to apply different logic if needed....
In the generic version of the formula, start = start date, end = end date, and dow = day of week. When given a date, WEEKDAY simply returns a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday and 7 = Saturday. So, 2 = Monday, 6 = Friday, and so on. The trick to this formula is understanding that dates in Excel are just serial numbers that begin on Jan 1, 1900....
which returns 7, since there are seven unique colors in B5:B16. Unlike the COUNT function, which counts only numbers, COUNTA counts both text and numbers. Since there are seven items in array, COUNTA returns 7. This formula is dynamic and will recalculate immediately when source data is changed. With a cell reference You can also refer to a list of unique values already extracted to the worksheet with the UNIQUE function using a special kind of cell reference....
In the example shown, the data validation applied to C5:C9 is: The ISTEXT function returns TRUE when a value is text and FALSE if not. As a result, all text input will pass validation, but numbers and formulas will fail validation. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....
In this formula, the FIND function is configured to search for the text “XST” in cell C5. If found, FIND will return a numeric position (i.e. 2, 4, 5, etc.) to represent the starting point of the text in the cell. If the text is not found, FIND will return an error. For example, for cell C5, FIND will return 5, since “XST” starts at character 5. The result returned by the FIND function is then evaluated by the ISNUMBER function....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
EOMONTH returns a serial number corresponding to an Excel date. To display the result as a date, apply a number format of your choice. Note: The EOMONTH function returns the last day of the month n months in the past or future. The EDATE function returns the same day of the month n months in the past or future. Example #1 - Basic usage With May 12, 2017 in cell B5: You can use EOMONTH to move through years as well:...
For example: In the example shown, the formula in D6, copied down, is: Notes: Only lowercase “j” and “i” are accepted by IMPRODUCT. Other values will result in the #NUM error. IMPRODUCT can handle up to 255 numbers Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....
To use PERCENTILE, provide a range of values and a number between 0 and 1 for the “k” argument, which represents percent. For example: You can also specify k as a percent using the % character: PERCENTILE returns a value greater than or equal to the specified percentile. In the example shown, the formula in G5 is: where “scores” is the named range C5:C14. Note: Microsoft classifies PERCENTILE as a “compatibility function”, now replaced by the PERCENTILE....
When data changes, you can simply refresh the pivot table to see a new summary. Pivot tables automatically group data using field values. For more details and examples, see Excel Pivot Tables. Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....
with these inputs, PRICEMAT returns a price for the bond of $93.09. Entering dates In Excel, dates are serial numbers. Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, you can use the DATE function. Basis The basis argument controls how days are counted. The PRICEMAT function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis....
The SCAN uses the LAMBDA function to apply the formula logic required. The LAMBDA is applied to each value, and the result from SCAN is an array of results with the same dimensions as the original array. Like the REDUCE function, SCAN iterates over all elements in an array and performs a calculation on each element. However, while REDUCE returns a single value, SCAN returns an array of values. The SCAN function takes three arguments: initial_value, array, and lambda....
Time can be supplied to the SECOND function as text (e.g. “7:45:30 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function. The SECOND function will “reset” to 0 every 60 seconds (like a clock). For example, given a time duration of 10 minutes, which is 600 seconds, the SECONDS function will return 0 (zero)....
On a Mac, check System Preferences > Keyboard > Shortcuts > Keyboard to confirm the shortcut is enabled. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
WEEKDAY supports several numbering schemes, controlled by the return_type argument. Return_type is optional and defaults to 1. The table below shows available return_type codes, the numeric result of each code, and which day is the first day in the mapping scheme. Note: the WEEKDAY function will return a value even when the date is empty. Take care to trap this result if blank dates are possible. Examples By default and without a value fore return_type, WEEKDAY starts counting on Sunday: To configure WEEKDAY to start on Monday, set return_type to 2: In the example shown above, the formula in D5 (copied down) is: The formula in E5 (copied down) is:...
FIND locates the “@” character inside the email address “achang@maaker.com”. The “@” character is the 7th character, so FIND returns 7: The number 7 is then subtracted from the 17, which is 10. The number 10 is used as the second argument for the RIGHT function, which extracts 10 characters from the email address, starting from the right. The complete formula is evaluated and solved like this: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
The TRUNC function simply truncates (i.e. removes) numbers; it doesn’t not round at all. TRUNC actually takes an optional second argument to specify the precision of truncation, but when you don’t supply this optional argument, it is assumed to be zero, and truncation happens at the decimal. What about INT or ROUND? You might wonder if you can use INT function, or the ROUND function instead. The behavior of INT is identical to TRUNC (with default settings) for positive numbers — the INT function will round a number down to the next integer and then return only the integer portion of the number....
where total is the named range C15. Note: the result is formatted with Percentage number format to show 36%, 18%, etc. The total already exists in the named range total (C15) which contains a formula based on the SUM function: In mathematics, a percentage is a number expressed as a fraction of 100. For example, 85% is read as “Eighty-five percent” and is equivalent to 85/100 or 0.85. To calculate the “percent of total” for a given expense, we need to divide the amount of the expense by the total of all expenses....
