Posts

Transcript In this video I’ll demonstrate how you can use VLOOKUP to join data in separate tables. In this worksheet we have two tables. In the first table, we have order data. You can see that we’ve got a date, customer id, product, and total. In a second sheet we have customer data. We’ve got first and last name, street, city, state and so on. And because the id is in the first column, we can use VLOOKUP to extract all of the data in this table to the right of the id....

Purpose Return value Syntax =IFNA(value,value_if_na) value - The value, reference, or formula to check for an error. value_if_na - The value to return if #N/A error is found. Using the IFNA function The IFNA function is designed to manage #N/A errors and ignore other errors. When a function returns an #N/A, it typically indicates that a value is not available or not found. In many cases, an #N/A error is useful information because it tells you the formula is not able to find a value....

Purpose Return value Syntax =IMLN(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMLN function The Excel IMLN function returns the natural logarithm of a complex number. For example, given the complex number “3+4i” as input, the function returns the logarithm of the complex number. =IMLN(COMPLEX(3,4)) // returns 1.6094379124341 + 0.927295218001612i The real part of the output is equal to the natural logarithm of the distance from the origin to the point “3+4i” in the complex plane....

Transcript Hi, Dave here from Exceljet! Welcome to our course on Excel shortcuts. Excel shortcuts are extremely powerful and they can really upgrade your skills in Excel. In fact, I’ve got 6 good reasons that Excel shortcuts they’re totally worth your time: Shortcuts allow you to work faster and more accurately in Excel Shortcuts really save you sanity you have to do a lot of tedious, repetitive work Shortcuts allow you to focus on the work, and not on Excel’s complicated interface....

Explanation This is an experimental formula to strip characters from text. The experimental part is using character codes instead of regular characters as a way to make the formula case-sensitive, and providing a way to reverse the logic of the formula with the “keep” input parameter. Unlike the formula explained here , this formula is not recursive. The formula takes four inputs: text - the incoming text chars - the characters to strip rep - the character to replace stripped characters with keep - strip or preserve chars (FALSE = strip, TRUE = preserve)...

Purpose Return value Syntax =LOOKUP(lookup_value,lookup_vector,[result_vector]) lookup_value - The value to search for. lookup_vector - The array or range to search. result_vector - [optional] The array or range to return. Using the LOOKUP function The LOOKUP function is one of the original lookup functions in Excel. You can use LOOKUP to look up a value in one range or array and return the corresponding value from another range or array. Like the newer XLOOKUP function, LOOKUP can look up values in either rows or columns....

Explanation A MAC (Media Access Control) address is a unique identifier assigned to most network adapters. Two common IEEE 802 standards display a MAC address in 6 groups of 2 hexadecimal digits separated by a colon (:) or hyphen (-) like this: "01-23-45-67-89-ab" "01:23:45:67:89:ab" To format a text string with 12 characters in the same way, you can use a formula like this: =TEXTJOIN(C5,1,MID(B5,SEQUENCE(6,1,1,2),2)) Working from the inside out, the SEQUENCE function is used to generate an array of 6 numbers used as the start_num argument in the MID function:...

Purpose Return value Syntax =NUMBERVALUE(text,[decimal_separator],[group_separator]) text - The text to convert to a number. decimal_separator - [optional] The character for decimal values. group_separator - [optional] The character for grouping by thousands. Using the NUMBERVALUE function The NUMBERVALUE function converts a text value representing a number into a valid numeric using custom decimal and group separators. You can use NUMBERVALUE to translate numbers from a locale-specific text format into a locale-independent number....

Sorry, we can’t find that page. Here are some other things that may be interesting to you. Excel Formulas Excel Functions Pivot Tables Conditional Formatting Excel Videos Excel Shortcuts Excel Video Training Other topics …

Purpose Return value Syntax =PHI(x) x - The value (z-score) for which you want the density of the standard normal distribution. Using the PHI function The PHI function calculates the value of the probability density function for a standard normal distribution at a given point. The standard normal distribution is a normal distribution with a mean of 0 and a standard deviation of 1. The PHI function is often used in statistical analysis, data science, and financial modeling to understand how likely a value is within a standard normal distribution....

Explanation Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a range to ISNUMBER (i.e. an array ), ISNUMBER will return an array of results. In the example, the range C5:C9 contains 5 cells, so the array returned by ISNUMBER contains 5 results: {FALSE;FALSE;FALSE;TRUE;FALSE} TRUE values represent numeric values. We want to know if this result contains any TRUE values, so we use the double negative operator (–) to force the TRUE and FALSE values to 1 and 0 respectively....

Purpose Return value Syntax =REPLACE(old_text,start_num,num_chars,new_text) old_text - The text to replace. start_num - The starting location in the text to search. num_chars - The number of characters to replace. new_text - The text to replace old_text with. Using the REPLACE function The REPLACE function replaces text at a specific location inside a text string. The location of the text to replace is given as a number representing the first character to replace, along with a character count to indicate how many characters to replace....

Explanation The Excel CEILING function rounds a number up to a given multiple. The multiple to use for rounding is given as the second argument ( significance ). If the number is already an exact multiple, no rounding occurs. CEILING works like the MROUND function , but unlike MROUND, which rounds to the nearest multiple, CEILING always rounds up to the given multiple. In the example shown, the formula in cell D6 is...

Purpose Return value Syntax =SIGN(number) number - The number to get the sign of. Using the SIGN function The SIGN function returns the sign of a number as +1, -1 or 0. If number is positive, SIGN returns 1. If number is negative, sign returns -1. If number is zero, SIGN returns 0. The SIGN function takes one argument, number , which must be a numeric value. If number is not numeric, SIGN returns a #VALUE!...

About This Shortcut This shortcut will allow multiple levels of undo; each time you use it Excel will step back one level. About This Shortcut This shortcut will allow multiple levels of redo’s; each time you use it Excel will step forward one level. Also, with certain actions (e.g. deleting a row), Excel will perform that action again. So, in some cases you can use this shortcut to quickly perform the same action again multiple times....

Purpose Return value Syntax =WORKDAY.INTL(start_date,days,[weekend],[holidays]) start_date - The start date. days - Working days before or after start date. weekend - [optional] Setting for non-working days. holidays - [optional] A list of dates that are non-working days. Using the WORKDAY.INTL function The WORKDAY.INTL function calculates a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. You can WORKDAY....

Explanation One of XLOOKUP’s features is the ability to lookup and return an entire row or column. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result. Note: XLOOKUP performs an exact match by default, so match mode is not set. Working from the inside out, the inner XLOOKUP is used to retrieve all data for “Frantz”:...

Purpose Return value Syntax =ACOS(number) number - The value to get the inverse cosine of. The number must be between -1 and 1 inclusive. Using the ACOS function The ACOS function returns the inverse cosine of a value. Input to the arc-cosine function must be between -1 and 1, inclusive. Geometrically, given the ratio of a triangle’s adjacent side over its hypotenuse, the function returns the angle of the triangle. For example, given a ratio of 0....

About This Shortcut This shortcut will apply a default Time format to selected cells. Excel offers many types of number formatting . About This Shortcut This shortcut will apply a Number format with two decimal places, thousands separator, and minus sign (-) for negative values. Excel offers many types of number formatting .

An array formula is a type of formula that performs an operation on multiple values instead of a single value. The final result of an array formula can be either one item or an array of items, depending on how the formula is constructed. For example, the following formula is an array formula that returns the sum of all characters in a range: {=SUM(LEN(range))} To work correctly, many (but not all) array formulas need to be entered with control + shift + enter ....