Posts

To allow only a date in the next 30 days, you can use data validation with a custom formula based on the AND, and TODAY functions. In the example shown, the data validation applied to C5:C7 is: The TODAY function returns today’s date (recalculated an on on-going basis). The AND function takes multiple logical expressions and returns TRUE only when all expressions return TRUE. In this case, we need to test two conditions: The first condition checks that the input is greater than today: The second condition checks that the input is less than today + 30: (Dates are just serial numbers in Excel, so we can simply add 30)....

For negative y-values the function returns a negative angle. An angle is measured from the positive x-axis direction with the positive direction in the counter-clockwise direction and the negative direction in the clockwise direction. Convert Output to Degrees To convert the output of the ATAN2 function from radians to degrees the formula is: Alternatively, the degrees formula can be used to convert the angle to degrees. Difference Between ATAN and ATAN2 For points in the first and fourth quadrant, the ATAN2 function returns identical output to the ATAN function....

Difference Between OR and XOR When a waiter in a breakfast diner asks if you want coffee OR orange juice, they are really asking if you want coffee XOR juice. You can have one or the other, but not both. 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....

The word “blank” is somewhat misleading in Excel, because a cell that contains only space will look blank but not be empty. In general, it is best to think of ISBLANK to mean “is empty” since it will return FALSE when a cell looks blank but is not empty. Examples If cell A1 contains nothing at all, the ISBLANK function will return TRUE: If cell A1 contains any value, or any formula, the ISBLANK function will return FALSE:...

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.

Examples The formula in D5 returns a 3 x 3 unit matrix : With 3 given for dimension, the MUNIT function returns a 3 by 3 array like this: The formula in J5 returns a 5 x 5 unit matrix. With 5 given for dimension, the MUNIT function returns a 5 by 5 array like this: Array syntax The MUNIT function returns an array of values. In Excel 365, where dynamic arrays are native, you can use the MUNIT function without any special handling – MUNIT will return an array of values that spill directly into cells in the worksheet....

When to use Excel OR Function Excel OR function can be used when you want to check multiple conditions. What it Returns It returns TRUE if any one of the conditions evaluates to TRUE, else it returns a FALSE. Syntax =OR(logical1, [logical2],…) Input Arguments logical1 – the first condition that you want to evaluate for TRUE or FALSE. [logical2] – (Optional) This is the second condition that you want to evaluate for TRUE or FALSE....

Once you are editing a cell, you can use the F2 / Ctrl+U again to toggle through available edit modes (edit, enter, point). “Point” and “Enter” work similarly and allow you to point to a cell. “Edit” mode simply moves the cursor in the formula text. Pressing F2 / Ctrl+U repeatedly will cycle through modes…you’ll see the mode displayed in the status bar, in the lower left corner. On the Mac, this doesn’t seem to work in certain areas, notably Define Names, and the New Formatting Rule window when adding a conditional format that uses a formula....

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 a Mac, the control key (⌃) can be used instead of the command key (⌘). 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.

with these inputs, the YIELDDISC function returns 0.03264023 which, or 3.26% when formatted with the percentage number format. 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. If you want to enter valid dates directly inside a function, the DATE function is the best approach. Basis The basis argument controls how days are counted....

Challenge What formula will translate the “N” and “Y” to weekday abbreviations as shown in the screenshot above? The workbook is attached below. Post your answer in the comments. Extra points for style and elegance, but workhorse solutions are fine, too :) Assumptions This would be a typical solution, and nicely illustrates how concatenation works. Note: you are free to use line breaks inside the formula bar to make formulas easier to read....

The challenge The data below represents orders, one order per row. There are three separate challenges. What formulas in F9, G9, and H9 will correctly count orders with the following conditions: The green shading is applied with conditional formatting and indicates matching values for each set of OR criteria in each column. For your convenience, the following named ranges are available: item = B3:B16 color = C3:C16 city = D3:D16 The worksheet is attached....

The results in column E are decimal values with the percentage number format applied. The general formula for this calculation, where “x” is the original number, is: Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns the original price for each item in the table, based on the percentages shown in column C and the current values in column D....

Here we have historical, 30-year mortgage interest rates going back to 1971. Let’s plot this data in a line chart. Line charts are especially good at visualizing trends over time. To start off, I’ll place my cursor anywhere in the data, then navigate to the insert tab and click the line chart icon. The first option is the one I want. The result is a simple line chart. This is a great example of the simplicity of a line chart....

In this tutorial, I will show you how to calculate IRR in Excel, how is it different from another popular measure NPV, and different scenarios where you can use in-built IRR formulas in Excel. Internal Rate of Return (IRR) Explained IRR is a discount rate that is used to measure the return of an investment based on periodical incomes. The IRR is shown as a percentage and can be used to decide whether the project (an investment) is profitable for a company or not....

This is mostly because dates can be formatted in many different ways in Excel. So while you may see two dates in Excel and think those are the same, there is a possibility that these might be different values in the back end (or vice versa, you may think two cells have different dates, and it may be the same). In this tutorial, I will show you a couple of techniques that you can use to compare dates in Èxcel....

Let’s take a look. So, we’ve looked at both relative and absolute references, and also at a situation where we needed to use both at the same time. These are sometimes called “mixed references.” A good example of a problem that requires a mixed reference is a multiplication table. If we enter a formula to multiply the numbers in column B by row 4, and use only relative references, we end up with huge numbers....

While the error is quite generic and doesn’t tell you what the problem is, in most cases, this happens when you give a formula a data type that is not expected (explained with examples below). For example, if you have a few numbers that you’re adding in Excel, and you also try and add a text value in the same formula, Excel will give you an error. This is because Excel expects only numbers to be used in addition or subtraction....