Fields The pivot table uses all three fields in the source data: Date, Sales, and Color: The Color field has been added as a Row field to group data by color. The Date field has been added as a Column field and grouped by year: The Sales field has been added to the Values field twice. The first instance is a simple Sum of Sales: The second instance of Sales has been renamed “Count”, and set to show a “Difference from” value, based on the previous year:...
which rounds prices as shown in the screenshot. Rounding rules In the example shown, the goal is to round prices to end in either .45 or .95, following these rules: To round up to the nearest half dollar, we use the CEILING function, with the significance argument set to .5: This will round the original price up to the next half dollar. For example, $4.31 will become $4.50, and $5....
When copied down the column, this formula will return a running count for each color in the Color column. In some versions of Excel, this is an array formula and must be entered with control + shift + enter. On the left side of the colon (:), the INDEX function returns a reference to the first cell in the column. This works because, the INDEX function returns a reference to the first cell, not the actual value....
where value is the named range E5. The result is a running count of the 4 cells that contain “blue”, since E5 contains “blue”. Basic count Normally, the COUNTIF function is given a range and criteria like this: Since the range B5:B16 contains 4 cells that contain “blue”, COUNTIF returns 4. The formula above works well when you want to single count for one value in a range. However, to get a running count, we’ll need to adapt the formula to use an expanding range....
which returns the total of items in “Red” columns for each row. Notice the criteria range, B4:G4 is locked as an absolute reference to prevent changes as the formula is copied. Totals for each color By carefully using a combination of absolute and mixed references, you can calculate totals for each color in a summary table. Notice in the example below, we are now picking up the cell references, I4, J4, and K4 to use directly as criteria:...
In the example shown, the formula in D5 is: lookup value comes from C5 table array is the named range “points_table” (F5:G7) column index is 2, since points are in column G range lookup is 0 (FALSE) to force exact match Because we are using VLOOKUP in exact match mode, the points table does not need to be sorted in any particular way. VLOOKUP is a nice solution in this case if you want to display the points table as a “key” for reference on the worksheet....
But one IF often leads to another, and once you combine more than a couple IFs, your formulas can start to look like little Frankensteins :) Are nested IFs evil? Are they sometimes necessary? What are the alternatives? Read on to learn the answers to these questions and more… 1. Basic IF Before we talk about nested IF, let’s quickly review the basic IF structure: The IF function runs a test and performs different actions depending on whether the result is true or false....
If C5/D5 returns a value, that value is returned. If C5/D5 returns an error, IFERROR returns an empty string (""). The IFERROR function takes two arguments: a value (usually entered as a formula), and a result to display if the formula returns an error. The second argument is only used if the first argument throws an error. In this case, the first argument is the simple formula for calculating the average order size, which divides total sales by the order count: The second argument is entered as an empty string ("")....
The result is 3, since there are three rows where Previous sales are greater than Current sales. SUMPRODUCT function The SUMPRODUCT function is designed to work with arrays. It multiplies corresponding elements in two or more arrays and sums the resulting products. One of SUMPRODUCT’s special features is that it can handle “array operations” natively, without requiring Control + Shift + Enter. This allows us to perform a comparison between current and previous sales directly in array1....
To allow a user to enter only dates between two dates, you can use data validation with a custom formula based on the AND function. In the example shown, the data validation applied to C5:C9 is: The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because we want to allow only dates in the month of June 2016, we give AND with two logicals....
Where data is an Excel Table based on the data in B5:D17. The result from SUMIF spills into the range G5:I9, and the results show the sum of Qty for each City and Size combination. Note: Dynamic Array Formulas are only available in Excel 365 and Excel 2021. Create the Excel Table One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed....
The CONCAT function accepts multiple arguments called text1, text2, text3, etc. up to 255 total. Arguments may be supplied as cell references, ranges, and hard-coded text strings. Only the first argument is required, and values are concatenated in the order they appear. For example, to concatenate the value of A1 and B1, separated by a space, you can configure CONCAT like this: This is equivalent to using the concatenation operator (&) manually like this: When concatenating numbers, number formatting is lost....
You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function. For example, to change the year of a date in cell A1 to 2020, but leave the month and day as-is, you can use a formula like this: See below for more examples of formulas that use the DAY 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.
Step-by-step example To illustrate how ISOMITTED works, imagine a simple LAMBDA formula that adds 10 any given value. With the value 100 in cell A1, this formula will return 110: Next, we alter the formula to make both a and b variables: With 100 in A1, if we supply 10 for b, the formula returns 110. If we supply 20 for b, the formula returns 120. So far so good. Now let’s say we want to make b optional, and we want b to default to 10 if not provided....
For example, a test score greater than 80% of all test scores is said to be at the 80th percentile. In this case, PERCENTRANK will assign a rank of .80 to the score. In the example shown, the formula in C5 is: where “data” is the named range C5:C12. Note: Microsoft classifies PERCENTRANK as a “compatibility function”, now replaced by the PERCENTRANK.INC function. Inclusive vs. Exclusive Starting with Excel 2010, the PERCENTRANK function has been replaced by two functions: PERCENTRANK....
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.
On the Mac, Ctrl + C also works 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.
On Windows, once you have the Paste Special Dialog open, you can type a letter to select the particular command you want, for example: F = Formula V = Values T = Formats C = Comments N = Validation H = All using source theme X = All except borders W = Column widths R = Formulas and number formats U = Values and number formats D = Add S = Subtract M = Multiply I = Divide B = Skip blanks E = Transpose...
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.
