Multi-Conditional IF Statement With Too Many Conditions?

Oct 21, 2013

So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )

The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]

My ridiculously large, almost functional function is:

=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony

[Code] ....

As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in.

View 3 Replies


ADVERTISEMENT

Multi Conditions Formating Without Conditional Formatting

Jan 23, 2008

I have a spreadsheet with data in columns A:K.

I want to be able to change font color for a row based on what is found in column J and K. In other words if J3 contains the letter M then row 3 should be Green. I know how to do this using conditional formating, however I have 2 problems.

First, I have 6 different conditions to meet.

Second, there is one more condition to meet. If the date in column K3 is older than todays date then row 3 should be turn Red regardless of what is found in column J3.

jamm

View 9 Replies View Related

Selecting A Result From Multi Conditions

May 5, 2009

I have attached a file, in which i need to get the value for IHC from a table using formula as the conditions will vary from time to time. The table is only a sample.. actually it had much more.

View 5 Replies View Related

Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met

Oct 17, 2007

I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard

View 2 Replies View Related

Create A Multi Condition IF Statement

Mar 26, 2009

I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.

What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".

Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".

If it is any day in between the two, it should display the term "Active"

I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.

=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))

=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))

(D# corresponds to the respective cell that contains the scheduled ISD)

View 13 Replies View Related

Fill Multi Columned ListBox Based On Range Meeting 2 Conditions

Mar 14, 2008

I'm trying to populate a dropdown Combo Box by using conditions.

In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels.

I've been trying to use worksheetfuntion.Index.

View 4 Replies View Related

Multiple Conditions Within An IF-THEN Statement

Jan 13, 2009

I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.

View 2 Replies View Related

IF Statement With List And Conditions

Oct 5, 2009

I am trying to create an IF statement that will double the unit value of a certain box (J15) if the number of I15 is less than or equal to 160 AND either "A,B,C, or D" is selected from a list in H15. If it does not qualify with both, it would just be the single value of J15, not doubled.

So far I can only get it to work with one item from the list in the IF statement:

=IF(AND(H15="A",I15<=160),J15*2,J15)

However, I don't know how to add in the other list options B-D and get it to work.

Basically I need these four statements combined into one:

=IF(AND(H15="A",I15<=160),J15*2,J15)
=IF(AND(H15="B",I15<=160),J15*2,J15)
=IF(AND(H15="C",I15<=160),J15*2,J15)
=IF(AND(H15="D",I15<=160),J15*2,J15)

View 4 Replies View Related

How To Insert More Conditions In If-statement

Feb 28, 2014

I am working on a search and replace macro. It's purpose is that within a region that varies only in row size, it's suppose to compare two values, A and B. A is only placed in one cell in the region, while B is a one column range in the region.

If A <> B, A should replace all B's in the region.

These regions are placed on top of each other in a long table. Please see the sample workbook, SampleDRW.xlsm

The code I am using is this:

[Code] ......

The problem is if the B value in the next region is the same as the one before but the A value change, my code don't pick up on it, because it only looks for changing B!

How can I expand the code to also pick up on this problem?

View 7 Replies View Related

IF Statement With Conditions: Start From Row 21

Jan 5, 2010

In the table I've created, I have a column (column A) that contains "Y" for the first 20 rows. I'd like to use an if statement in another column (column B) to create a numbered list starting with the first row (from column A) that is blank. For example, if column A contains "Y" in the first 20 rows, I'd like starting in row 21 (in column B) to return a value of "1" and then have the subsequent rows increase in value by 1, so row 22 would be "2", row 23 would be "3" etc. The catch is, if for some reason I make a change in column, let's say extend the "Y"s down to row 25, I'd like column B to reflect those changes by starting the list in row 26.

View 3 Replies View Related

If Statement Using Left And Right Conditions

Feb 16, 2010

I'm tring to put data in empty cells using an formula. =IF((LEFT(A4,5)),"Class",(RIGHT(A4,5)))

View 3 Replies View Related

Two Conditions Not Talking To Each Other In IF Statement

Jul 17, 2012

Formula I've been labouring over which is only bringing back partial results - I don't normally have much call in my role to build nested formulas...

The following is what I have so far:

=IF(AND(AQ2="MortgageSession",OR(J2="LeedsCounselling",J2="Glasgow",J2="Limavady")),
"Mortgage",IF(AND(AQ2="SelfEmployedSession",J2="LeedsCounselling"),"Mortgage","Other"))

The formula is made up of two conditions:

1.
IF AQ is 'MortgageSession'
AND
J2 is 'LeedsCounselling'
OR Glasgow
OR Limavady

I want the cell to return 'Mortgage'

2.
IF AQ is 'SelfEmployedSession'
AND
J2 is 'LeedsCounselling'

I want the cell to also return 'Mortgage'

Everything else 'Other'

I've managed to get the formula to work to a point where it will happily bring something back without error boxes, however it is only bringing back the second condition and ignoring the first - something to do with the join in between these?

View 7 Replies View Related

Include Two Conditions Into IF Statement

Jan 17, 2007

I am trying to include two conditions into this IF statement.

=IF(ISBLANK('SHEET1'!W8),0,1)

I want to include the cell X8 in this aswell.

View 9 Replies View Related

Switch Off Conditions In And Statement

Jan 15, 2010

I have multiple anded conditions both Boolean and numerical. For testing purposes, I wanted to be able to switch off individual numerical conditions in the If statement. It seemed like an easy matter that I could simply comment out the And statement of choice and recompile. When I tried this, I received a compile error.

Another option would be to use individual togglebuttons to selectively turn off the 5 numerical comparisons. I have no idea how to set that up.

how to do this with either commenting or togglebuttons?

The If statement
If OptionButton4.Value = True _
And ToggleButton1.Value = True _
And Range("G3").Value >= Range("N6").Value _
And Range("AV22").Value >= Range("AU22").Value _
And Range("E3").Value = "Long" _
And Range("G3").Value Range("BG7").Value _
And Range("H4").Value >= Range("L4").Value _
And TradeFound = False And _
DeletedFound = False Then

View 9 Replies View Related

If Statement With Multiple Conditions

May 2, 2006

Dim b As Long
Dim last_rowB As Long
last_rowB = Range("K65536").End(xlUp).Row
For b = last_rowB To 2 Step -1
If Sheets("Main").Cells(b, 11).Value = Sheets("Main").Cells(b + 1, 11).Value & Sheets("Main").Cells(b, 11).Value <> "" Then
Sheets("Main").Cells(b + 1, 11).EntireRow.Delete
End If
Next b

If 2 cells are the same i want to delete the row of the 2nd cell, this works. But i dont want 2 delete cells that are blank, this doesnt work. For some reason it deletes everything in the table.

View 3 Replies View Related

Multi-Conditional Average

Mar 10, 2009

See attached example for reference - I am trying to calculate an average if it meets 2 conditions, i.e, calulate the average for the Lead Time column if it is a Bag and On Time. Hope you guys can come through as always!

View 4 Replies View Related

Multi-Conditional With Wildcards...

Jun 23, 2009

I've got a column with client codes (col. B) and another one with material codes (col. C). Both columns mix numbers and text. First condition: If cells in "B" column equal a specific code ("DA2")

Second condition: AND If the cells in "C" column start with a certain pattern ("30") followed by some other pattern (four random digits, a dash, and two or three more random digits). THEN a new cell should say "T1", Else it should just copy another cell.

View 5 Replies View Related

IF Statement (counters In It To Start When The Conditions Is Met)

Jul 7, 2009

I have a series of IF statements in a formula. I need one of the counters in it to start when the conditions is met.

i.e. the false section in an IF is along the lines of $B$3+J2, and then it continues down the column $B$3+J3 then $B$3+J4 etc. I need the J# to start increasing when the condition within the IF statement is met, not from the very beginning. When I get to, let's say the 13th row, when the condition is met, I want J2, not J13or14.

View 2 Replies View Related

Adding Additional Conditions To IF Statement

Dec 29, 2011

I need to add a condition to a statement that adds a "If TRUE" reference to another cell.

The other cell can be either a TEXT value "RM1" OR "TE2". If neither condition is true than the value is blank or zero.

So right now I have in the destination cell =IF(AB7="CONT",V7,"") That works lovely, but really isn't countable unless cell "Z7" ALSO has Either "RM1" OR "TE2".

If cell "Z7" has the text "DEA" or "SP" then the value of "V7" will be placed in a different column. I will use a different destination column (for this modified "DEA" or "SP" condition)

View 7 Replies View Related

Multi-Conditional Row Deletion With Dates

Apr 3, 2009

Here are the examples (pictures):

Example 1 Before
Example 2 Before

You can see that it's an history of currency trades (Forex). What I want is to see how much money one would have made letting only one open position per currency pair. A basic algorithm would be something like this:

View 5 Replies View Related

If/Then Multi-cell Conditional Formatting

Nov 26, 2006

I have an interesting problem on my hands. I work for a gas station, and for our cigg orders, we fax an excel speadsheet to SAMS CLUB. I recently have done some modifications by applying conditional formating to indicate negitive numbers and zeros, however, I am trying to find a way to blank out 2 adjacent cells when a cell = 0.

Here is a sample:

Order# Desc #to order

921934 (brown) Bx 0

The Zero is already whited out in my sheet, however I am trying to find a way to make the Order Number and Desc. be whited out also when the Number to order = 0

View 14 Replies View Related

Multi-Conditional Table Formula

Feb 15, 2010

I'm trying to add "yes" as per the attached spreadsheet, using forumla. Account codes are duplicated (sometimes more than once, although no shown on the example) each account code needs to have the same yes column.

View 3 Replies View Related

If Statement (sort Data Similar To Below Using The Following Conditions)

Oct 26, 2008

I need to sort data similar to below using the following conditions. If the total of A's is greater than 4 they get an A. If the total of E's is greater than 4 they get an E. If they have more A's than E's, assuming they have at least 4 of each then they get an A. If the total is 5, but they don't have 4 A's then they get an E

NAME E'sA'sTOTAL
John 235 ( so this one should get E)
Mary 459 ( This one should get A)
Tim 112 (This one shouldn't get anything)
Jane 145 ( This one should have an A)
Sam 415 (This one should have an E)

I would like this to sort in one cell - I can do this over 5 cells - but not in one.

View 3 Replies View Related

Multiple If Statement: Value Returned In Cell C3 If Two Conditions Are Met

Aug 27, 2009

I am trying to have a value returned in cell C3 if two conditions are met. In Cell C3 (Sheet 1), if the value in Cell A3 (Sheet 1) is listed in column A on sheet 2, and the 12031 is listed in column B on sheet 2, I need the value of Open to be returned.

View 3 Replies View Related

Multi Conditional Formula For Year And Month

Mar 22, 2005

I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to.

************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12=
ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]

View 9 Replies View Related

Multiple Conditions For If Statement For Past And Future Dates

Feb 14, 2014

I need creating a formula that combines the following if statements in cell C107:

=IF(AND(B107

View 1 Replies View Related

If Statement (2 Conditions): Cardboard Manufacturing Plant In The Q.A Department

Jan 29, 2007

I work at a cardboard manufacturing plant in the Q.A Department. Occasionally paper claims need to be raised due to the paper being faulty (out of spec.). I have attached a spreadsheet which at present works out the paper cost according to which paper grade is typed into a particular columb. But, paper price varies according to the width (i.e. Deckle) of the paper as well. And this is the variable which I have not been able to implement thus far.

The relevant columbs are 'D', 'E' & 'I' for the purposes of this formula... It should be noted that below this I have pasted in the paper pricing list... and that in general each paper grade has 3 different prices depending on the Deckle (i.e. paper width), so there are 3 possible prices which can be displayed depending on the Deckle inputted into columb 'E'.

View 2 Replies View Related

IF Statement Does Not Produce Required Results: Resulting In Zero Values In Most Conditions

May 25, 2009

I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.

Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.

View 3 Replies View Related

Multi Conditional IF Function: Show Where The Tender Ranks Against A Set Of Criteria

Nov 12, 2009

I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.

View 3 Replies View Related

Conditional Formatting With 2 Or 3 Conditions?

Jun 9, 2014

In the attached spreadsheet, the numbers in col A derived from another spreadsheet. The table D1:L21 is precaculated based on projection. I need 2 cells highlighted when they capture the number from the same row (col A). In another word, whenever the number from col A fall in between the 2 cells in the same row, they will be highlighted. Another condition is when it is smaller then 10 reps, the corresding cell in col D will be highlighted, as demonstrated in the attached table. I tried AND(E2<A2, F2>A2) but it didn't work. I also tried LARGE((E2:L2<A2)*(E2:L2),1) and similar SMALL function. They work as an array in normal excel, but not in the conditional formatting.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved