Using IF with AND, OR, and NOT functions in Excel
Technical Details. Here are overviews of how to structure AND, OR and NOT functions individually. When you combine each one of them with an IF statement, they read like this: AND – =IF (AND (Something is True, Something else is True), Value if True, Value if False)
Use Goal Seek to find the result you want by adjusting an input …
If you know the result you want from a formula but aren't sure which input is needed to get that result, use Excel's Goal Seek feature.
Introduction to What-If Analysis - Microsoft Support
You can use Goal Seek to determine what interest rate you must secure in order to meet your loan goal. Cells B1, B2, and B3 are the values for the loan amount, term length, and interest rate. Cell B4 displays the result of the formula =PMT(B3/12,B2,B1) .
Look up values with VLOOKUP, INDEX, or MATCH - Microsoft …
Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.
Use Goal Seek to find the result you want by adjusting an input value
On the Home tab, in the Number group, click Percentage. Click Increase Decimal or Decrease Decimal to set the number of decimal places. If you know the result you want from a formula but aren't sure which input is needed to get that result, use Excel's Goal Seek feature.
Define and solve a problem by using Solver - Microsoft Support
Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet.
Group or ungroup data in a PivotTable - Microsoft Support
Grouping data in a PivotTable can help you show a subset of data to analyze. For example, you may want to group an unwieldy list date and time fields in the PivotTable into quarters and months.
Excel video training - Microsoft Support
A list of Excel training, from entry level to advanced skills, plus templates and other resources.
Use nested functions in a formula - Microsoft Support
Examples. The following shows an example of using nested IF functions to assign a letter grade to a numeric test score. Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter.
Create a parameter query (Power Query) - Microsoft Support
Parameters can be used in any query step. In addition to functioning as a data filter, parameters can be used to specify such things as a file path or a server name. Parameters don’t prompt for input. Instead, you can quickly change their value using Power Query.
FILTER function - Microsoft Support
The FILTER function allows you to filter a range of data based on criteria you define. In the following example we used the formula =FILTER (A5:D20,C5:C20=H2,"") to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string (""). Syntax.