How to Fix an Excel IF and AND Formula Returning Only One Result
Question details
The user needs to correct an IF and AND formula that incorrectly outputs a single result for a range instead of evaluating each row independently.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Applying an IF and AND logical test across multiple rows or a data range in a spreadsheet.
- Observed behavior
- The formula evaluates the entire specified range as a single logical test, returning only one result instead of processing and outputting a result for each row individually.
Identify whether you want to calculate results row by row using the fill handle or apply a single array formula to calculate multiple cells at once. Ensure your data ranges are identical in size to avoid calculation errors.
Use Single-Cell References and Fill Down
The easiest way to evaluate multiple rows is to write the formula for the first row using single-cell references and then copy it down the column.
Click on the first cell in your column where you want the result to appear (for example, D2).
Type the formula using single-cell references, such as =IF(AND(C2=A2+2,B2=A2+1),100,999).
Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to your last row (e.g., D5) to evaluate each row independently.
Use an Array Formula for Multi-Cell Calculation
If you prefer using range references instead of filling down, use a boolean logic array formula with multiplication instead of the AND function.
Fix Formula Errors Instantly with WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet environment that handles both standard cell references and advanced array formulas effortlessly. It is perfect for managing complex logical tests without unexpected errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open your document containing the target data.
- 2. Apply the formula: Enter the IF and AND formula in the first row of your target column.
- 3. Drag to fill: Use the fill handle to drag down and evaluate each row independently.
Frequently Asked Questions
Why does the AND function return only one result when using ranges?
The AND function is inherently designed to test all conditions within its arguments and return a single TRUE or FALSE. When given a range, it evaluates the entire range as a whole rather than processing it item by item.
Can I use the AND function inside an array formula?
No, the standard AND function does not return an array of results. To perform an 'AND' operation within an array formula, you must multiply the conditions together, such as (Condition1)*(Condition2).
What happens if my data ranges are different sizes in the array formula?
If the ranges are mismatched in size, the array formula will return an #N/A error. Always ensure that the ranges you are comparing have the exact same number of rows and columns.
