» Simplifying Formulas by Reducing the Complexity of IF Functions
CATEGORY - Logical Formulas
VERSION - All Microsoft Excel Versions
The range A2:A4 lists the three primary colors.
To find which of the various colors in column B are primary colors, we could use the following IF formula:
=IF(OR(B2=A2,B2=A3,B2=A4),"Primary Color","Not a Primary Color").
However, such a formula would become increasingly complex as the number of values in column A increases.
Solution:
Use the IF and OR functions as shown in the following Array formula:
{=IF(OR(B2=$A$2:$A$4),"Primary Color","Not a Primary Color")}
Book Store:
Recommended Books:
- Marketing Plan: A Handbook with Marketing Plan
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
- Not-for-Profit Accounting Made Easy
- Positioning: The Battle for Your Mind
- Guide to Financial Reporting and Analysis
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
multiple if & and statements combined
alan tenore
Considered an expert by most, I can not get multiple if with and statements to work. I am doing a 2 by 4 matrix. if a1<1 and b1>1 then 5 if a1<1 and b1 <1 then 0 --- and so on
a1could be 0,0,1,1 b1 could be 1,1,2,2
any ideas
help with IF function
Mary Leveridge
I am a basic excel user and need a function that will look at two cells, see if either is greater that 1-00-5999.00 If so, the result should be DC, if not it should be GL. Here is what I tried and keep getting errors
=IF(M1:N1>1-00-5999.00,"DC","GL")
can anyone tell me what I am doing wrong?
help with IF function
Mary Leveridge
I am a basic excel user and need a function that will look at two cells, see if either is greater that 1-00-5999.00 If so, the result should be DC, if not it should be GL. Here is what I tried and keep getting errors
=IF(M1:N1>1-00-5999.00,"DC","GL")
can anyone tell me what I am doing wrong?
If statements
Donnaend
I would like to modify my employee timesheets to take the sum of their hours column and then if the amount is equal to or over 40 hours only display 40 and if the amount is less than 40 display that amount. Can anyone help?
oldchippy
[QUOTE=Donnaend]I would like to modify my employee timesheets to take the sum of their hours column and then if the amount is equal to or over 40 hours only display 40 and if the amount is less than 40 display that amount. Can anyone help?[/QUOTE]
You could try something like this,
=IF(SUM(C1:C5)>=1.666667,1.666667,SUM(C1:C5))
Change the reference to C1:C5 to your reference for range of hours to sum, then Custom format the cell to [hh]:mm
v srinivas 2365
murali