[excel] `IF` statement with 3 possible answers each based on 3 different ranges

I have 3 ranges of numbers and the answer depends on the range.

75-79=0.255

80-84=0.327

85+  =0.559

I tried to create an equation that accounts for the ranges, however Excel states that I have entered too many arguments for this function. Below is the equation that I entered that is not working. (X2 contains the number)

=IF(X2=75,X2<=79,0.255,IF(X2=80,X2<=84,0.327,IF(X2>=85,0.559,0)))

I also tried to enter the range of numbers into another sheet - Age, and got an error #Value!.

=IF(X2=Age!A1:A5,0.257,IF(X2=Age!A6:A10,0.327,IF(X2=Age!A11:A33,0.559,0)))

This question is related to excel if-statement worksheet-function

The answer is


Your formula should be of the form =IF(X2 >= 85,0.559,IF(X2 >= 80,0.327,IF(X2 >=75,0.255,0))). This simulates the ELSE-IF operand Excel lacks. Your formulas were using two conditions in each, but the second parameter of the IF formula is the value to use if the condition evaluates to true. You can't chain conditions in that manner.


This is what I did:

Very simply put:

=IF(C7>100,"Profit",IF(C7=100,"Quota Met","Loss"))

The first IF Statement, if true will input Profit, and if false will lead on to the next IF statement and so forth :)

I only have basic formula knowledge but it's working so I will accept I am right!


You need to use the AND function for the multiple conditions:

=IF(AND(A2>=75, A2<=79),0.255,IF(AND(A2>=80, X2<=84),0.327,IF(A2>=85,0.559,0)))

Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to if-statement

How to use *ngIf else? SQL Server IF EXISTS THEN 1 ELSE 2 What is a good practice to check if an environmental variable exists or not? Using OR operator in a jquery if statement R multiple conditions in if statement Syntax for an If statement using a boolean How to have multiple conditions for one if statement in python Ifelse statement in R with multiple conditions If strings starts with in PowerShell Multiple conditions in an IF statement in Excel VBA

Examples related to worksheet-function

WorksheetFunction.CountA - not working post upgrade to Office 2010 Excel formula to get cell color How to find the first and second maximum number? IF statement: how to leave cell blank if condition is false ("" does not work) Use string value from a cell to access worksheet of same name I want to compare two lists in different worksheets in Excel to locate any duplicates Converting a string to a date in a cell Refer to a cell in another worksheet by referencing the current worksheet's name? Have Excel formulas that return 0, make the result blank Convert date field into text in Excel