The chart type is 100% stacked bar, and its a good example of how a bar charts can accommodate extremely long labels. The data used in the chart looks like this: How to make this chart 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....
If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string (""), which looks like a blank cell. Boolean logic is a technique of handling TRUE and FALSE values like 1 and 0. In cell C5, the formula is evaluated like this: In other words, if C5 < 100, output “low” 1 time. In cell C6, the formula is evaluated like this: In other words, if C6 < 100 is FALSE, output “low” zero times....
Which can be copied across row 8 to pickup every 3rd value from row 5. The OFFSET function is designed to create references by using “offsets” from a starting cell. In the example shown, the starting cell is C5, provided to OFFSET as an absolute reference so it won’t change as the formula is copied: For the rows argument, we provide zero, since we want to stay in the same row....
In the example shown, the formula in F8 is: The first expression tests every holiday date to see if it’s greater than or equal to the start date in F5: This returns an array of TRUE/FALSE values like this: {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE} The second expression tests every holiday date to see if it’s less than or equal to the end date in F6: which returns an array of TRUE/FALSE values like this: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE} The multiplication of these two arrays automatically coerces the TRUE/FALSE values to ones and zeros, resulting in arrays that look like this: After multiplication, we have just one array like this: Finally, SUMPRODUCT sums the items in the array and returns 4....
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.
Discount rate is based on the concept of the time value of money. The discount rate is the interest used to convert between future value and present value. A future value can be “discounted” by a given interest rate to determine the present value. Example In the example shown, we want to find the discount rate for a bond with a price of $89.50 and a redemption value of $100....
In fall 2018, Microsoft announced a new feature in Excel called “dynamic array formulas”. In a nutshell, this feature allows formulas to return multiple results to a range of cells on the worksheet based on a single formula entered in one cell. This behavior is called “spilling” and results appear in a “spill range”. All results are dynamic – if source content changes, the results dynamically update to stay in sync....
FACT takes just one argument, number, which should be a positive integer. If number is not an integer, the decimal portion of number will be removed before the factorial is calculated. Examples If number is not an integer it will be truncated to an integer: When number is zero, FACT returns 1: If number is negative, FACT returns the #NUM! error: Notes Number cannot be negative or FACT will return the #NUM error....
Note: the NPER function returns the number of periods for a series of cash flows (like a loan repayment schedule) whereas PDURATION returns the number of periods for a single sum to achieve a certain amount. Example Assume you have $5,000 to invest at an annual rate of 5%. When interest is compounded monthly, how long will it take for the initial investment of $5,000 to reach $10,000? In the example shown, the formula in F5 is: Notice because rate must be provided as interest rate per period, it is given here as the annual interest rate divided by the number of compounding periods per year (12)....
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.
Flash fill is a new feature added in Excel 2013. 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.
Note: In Excel 365, the original “comment” was renamed to “note” and a new “threaded comment” was added to allow back and forth comments by reviewers. 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.
We also have some nice laminated shortcut cards.
The WEEKNUM function accepts two arguments, serial_num and return_type. The serial_num argument must have a valid Excel date. The return_type argument controls what day of the week begins a new week number. Return_type is optional and defaults to 1, which sets new week numbers to start on Sunday. When return_type is set to 2, week numbers begin on Monday. With a return_type of 1-17, week number 1 in a given year is assigned to the week that contains January 1....
Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make a selection that includes more than one row, enter the formula, and confirm with Control + Shift + Enter. This is formula will continue to generate relative numbers even when the range is moved. However, it’s not a good choice if rows need to be sorted, deleted, or added, because the array formula will prevent changes....
The WORKDAY function returns a date in the future or past that takes into account weekends and, optionally, holidays. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days. Now, to review, date calculations can be really simple. To add one day to the date in B5, I can put “1” in C5, and then just use the formula: =B5 + C5 And this gives us December 23rd, as expected....
