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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies
View Related
Feb 14, 2012
I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
View 9 Replies
View Related
May 5, 2014
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
View 5 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
Mar 18, 2014
I am looking to have one formula containing two If Statements that minus.
=IF(B6="investment",C6,0)-IF(B7="gross",C6,0)
So the result of If Statement 1 minus the result of If Statement 2.
What I am seeing at the moment in the cell is FALSE and what I want to see is the sum.
View 2 Replies
View Related
Jun 13, 2009
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
View 3 Replies
View Related
Apr 25, 2007
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
i.e.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
View 9 Replies
View Related
Jul 3, 2007
I have a problem with a formula inserted with control+shift+enter
The formula is
MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999))
In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.
Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.
The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.
I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
View 7 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Aug 14, 2006
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock #QTYMonth
01335208Aug-06
01337402Aug-06
01337392Aug-06
01335400Sep-06
01337100Sep-06
01337102Sep-06
If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock #Aug-06Sep-06
01335208400
01337794202
View 13 Replies
View Related
Mar 13, 2008
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
View 9 Replies
View Related
