Posts

Excel INT Function Overview When to use Excel INT Function INT Function is used when you want to get the integer portion of a number. What it Returns It returns an integer number. Syntax =INT(number) Input Arguments number – the number for which you want to get the integer value. Excel INT Function Examples Below are three examples of using the INT function in Excel. Example 1 – Getting the Integer Part from a Positive number When you use INT function with a positive integer, it discards the decimal part and gives you the integer part of the number....

Adding symbols into your tables or charts can be a great visual aid to your numbers. In this example we’ll look at how we can add up and down arrows into our number formatting to show increases or decreases in our data. The symbols we’re going to use are unicode characters which are actually a standard across any platform and are available on any language PC. Step 1: Accessing Symbols in Excel We’ll need to insert the symbols we want to use into our workbook....

Syntax IFERROR(Expression, Value if error) Expression (required) – This is part of the formula that is checked for an error.Value if error (required) – This is the value that is returned if the expression results in an error (#DIV/0, #N/A, #NAME, #NULL!, #NUM!, #REF!, or #VALUE!). Example In this example we want to calculate an average cost per use = cost / # of uses (=A2/B2), however some of the # of uses are 0 and this results in a #DIV/0 error....

where item is the named range B5:B12, price is the named range D5:D12, and data is sorted ascending by date. First, this expression is evaluated: When F7 contains “sandals” the result is an array of TRUE and FALSE values like this: This array is provided as the divisor to 1: The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so the result is another array like this: returned directly to the LOOKUP function as the lookup vector argument....

where data is an Excel Table in the range B5:D15. XLOOKUP function The XLOOKUP function is a modern replacement for the VLOOKUP function. A key benefit of XLOOKUP is that it can handle array operations as the lookup_array or return_array. This means we can construct the lookup_array we need as part of the formula. We start off by providing lookup_value as 1: Note: The lookup value of 1 has nothing to do with the value for n, which is also 1 in this case....

Where dates (B5:B15) and values (C5:C15) are named ranges. Note: this is an array formula and must be entered with Control + Shift + Enter. This results in an array like this: which is then compared to the text in F4, “Mon”. The result is another array, which contains only TRUE and FALSE values: Note each TRUE corresponds to a Monday. This array is returned inside the IF function as the logical test....

where data (B5:F15) and header (B4:F4) are named ranges. In a standard configuration, the INDEX function retrieves a value at a given row and column. For example, to get the value at row 2 and column 3 in a given range: However, INDEX has a special trick – the ability to retrieve entire columns and rows. The syntax involves supplying zero for the “other” argument. If you want an entire column, you supply row as zero....

As the formula is copied down the column, it returns the correct number for each month. Working from the inside out, we start by concatenating the name in cell B5 to the number 1: This expression returns a string like “January1”, “February1”, “March1”,and so on. It turns out, that if we pass a date fragment like this into the MONTH function, it will coerce the string to a valid date, using the current year....

Note: this formula is not case-sensitive, you can find a case-sensitive formula here. The COUNTIF then returns a count of any cells that do not contain “OK” which is compared to zero. If the count is zero, the formula returns TRUE. If the count is anything but zero, the formula returns FALSE. Ignore empty cells To ignore empty cells, you can use a more generic version of the formula: This formula generates a count of all matching values, and compares that count to a count of all non-empty cells....

The results in column E are decimal values with the percentage number format applied. Converting this to an Excel formula with cell references, the formula in E5 becomes: When the result the Percentage number format is applied, 0.75 is displayed as 75%. As the formula is copied down, the formula returns percentage sold for item in the table. Formatting percentages in Excel In mathematics, a percentage is a number expressed as a fraction of 100....

Note: data ends on row 18, so the calculation is as follows: $1,006.75 / 739 = $1.36 Fields The source data contains three fields, Product, Quantity, and Sales. A fourth field called “Unit Price” is a calculated field. The calculated field was created by selecting “Insert Calculated Field” in the “Fields, Items, and Sets” menu on the ribbon: The calculated field is named “Unit Price” and defined with the formula “=Sales/Quantity” as seen below:...

Fields The source data in the example contains three fields: Area, Item, and Cost. The pivot table as shown uses only two of these fields: Area and Cost. Area is a Row field, and Cost has been added twice as a Value field, once to show total cost, once to show a percentage breakdown. To show total estimated cost, the Cost field is set to sum values and has been renamed “Estimate”:...

Unless you’re among the few lucky ones, you’ll most likely get your data in a format that would need some cleaning. One common use case of this would be when you get a dataset and you have to remove some characters from the left for this dataset. These could be a fixed number of characters that you need to remove from the left, or could be before a specific character or string....

The name “list” is a named range B5:B14. Named ranges are absolute references by default, so be sure to use an absolute reference if not using a named range. The second part of the formula is an expression that works out the correct row number as the formula is copied down: The result of this expression is a single number starting at 10, and ending at 1 as the formula is copied down....

When a dynamic array formula outputs multiple values, it is said to “spill” these values onto the worksheet. For example, if I use the UNIQUE function on this list of colors, UNIQUE spills 3 values - red, blue, and green. The rectangle that encloses these values is called the “spill range”. Notice when I select any cell in a spill range, the rectangle will appear with special highlighting. The spill range for a given formula is dynamic, and may expand or contract as source data changes....

This function will allow you to find and replace a piece of text within a text string. You can either replace all occurrences of the piece of text or just a particular instance of it. Syntax SUBSTITUTE(Text, Find, Replace, Instance) Text (required) – This is the text you are going to search.Find (required) – This is the piece of text you want to find.Replace (required) – This is the piece of text you want to replace it with....

where “data” is an Excel Table in the range B5:D16. In this configuration, VLOOKUP matches the text “RED” in row 5 of the table and returns 10 from the Qty column in the same row. This is an array formula and must be entered with control + shift + enter, except in Excel 365 or Excel 2021. Note: With XLOOKUP, or INDEX and MATCH, the approach is the same but the formulas are more straightforward: XLOOKUP example, INDEX and MATCH example....

Microsoft applications such as Word, Excel, PowerPoint are used in almost every large organization as well as by millions of small businesses, freelancers, and entrepreneurs. For something that is so popular, it makes sense to have a decent knowledge on how to use these applications efficiently. In some job roles, a major part of the work involves working with these applications on a daily basis. I have been part of many interview panels where I have seen the candidates mention Excel as one of the skills they are expert in, but when asked a few questions on it, the expertise fizzled out....

But finding and removing them from your data is actually quite easy in Excel. In this tutorial, we are going to look at 7 different methods to locate and remove duplicate values from your data. Video Tutorial What Is A Duplicate Value? Duplicate values happen when the same value or set of values appear in your data. For a given set of data you can define duplicates in many different ways....

It’s pretty common to want to know what day of the week a given date falls on and unless you’ve got some sort of gift for knowing that, you’re going to need a way to figure it out. In Excel, there are many different ways to determine this. In this post, we’re going to explore 7 ways to achieve this task. Format a Date as the Weekday Name The first option we’re going to look at involves formatting our date cells....