» 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:
- Windows XP Annoyances
- Keys to Reading an Annual Report (Barron's Business Keys)
- The New Financial Order: Risk in the Twenty-First Century
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
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