# Under What Conditions Does A Logical Test

Feb 7, 2010Under what conditions does a simple logical test, like

View 5 RepliesUnder what conditions does a simple logical test, like

View 5 RepliesADVERTISEMENT

I am trying to create a logical test with three possible results:

h2 <10.2 "needs improvement"

(if false) h2=10.2 "meets standard"

if false h2>10.2 "exceeds standard"

Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.

Here is the formula I am trying to write:

=IF(A21=(VLOOKUP(XX52,AN24:AN52,0,V38),V45,0)

The above is in cell X45

This does not work, but this is what I want it to do:

I have a list of text, if cell A21 equals a range in that text then I want the cell to return whatever is in Cell V45, if A21 does not equal within the range of text, then I want it to return a zero in cell X45.

You can see that I tried using Vlookup as the logical test, but I am not doing something right or vlookup cant be used that way.

I am trying to build a logical formula and it turns wrong values. I have four criteria in numbers, the codes are: 1 invoices, 3 credits, 7 Debits, 8 Returns, 9 Payments. All of these codes have positive numbers. I am trying to convert "credits", "returns", "payments" in negative . the rest "invoices" "debits" stay positive. In column A i have dollar values all positive, column B "codes" mentioned above, column C "i want to put the correct values.

View 9 Replies View RelatedI have a spreadsheet where I have a list of patients who were evaluated on a certain date unique to that patient. Some of the patients have a subsequent date of death, and some do not (they're still alive).

I need to create a logical test which indicates whether or not the patient died at specific intervals after their unique evaluation date-- at 3 months, 6 months, and 12 months. Additionally, I need to measure whether the patient died at 0-6 months, or 0-12 months, or is still alive.

In other words, if Patient A was evaluated on 1/1/2008 and subsequently died on 10/31/2008, I need to do a logical test that says 3 months = false (no death), 6 months = false (no death) and 12 months = true (death). True = 1 and False = 0 (so I can do a simple column add to figure out how many died at each interval). Additionally, for the 0-6 month test it should calculate = 0, but for the 0 - 12 month test it should calculate = 1.

I'm totally stumped as to how to do this. Currently my spreadsheet reads as follows: (I've put ** next to cells that I need formulas to calculate. If no **, assume it's a value I've inputted manually)

A1 = Patient number (numerical order)

B1 = Patient Initials

C1 = Initial Evaluation date

D1 = Date of death (if any- blank if patient is still alive)

E1 = Patient death indicator (1 = dead, 0 = alive)

F1** = Indicate whether or not patient died within 3 months of evaluation (1 = dead, 0 = alive)

G1** = Actual date of death within 3 month interval

H1** = Indicate whether or not patient died within 6 months of evaluation (1 = dead, 0 = alive)

I1** = Actual date of death within 6 month interval

J1** = Indicate whether or not patient died within 12 months of evaluation(1 = dead, 0 = alive)

K1** = Actual date of death within 12 month interval

L1** = Did patient die within 0-6 months of evaluation? (1 = dead, 0 = alive)

M1** = Actual date of death within 0-6 months

N1** = Did patient die within 0-12 months of evaluation? (1 = dead, 0 = alive)

O1** = Actual date of death within 0-12 months

I have data in column A. The data contains a name, an underscore and initial/s. For example: rebecca_rt, john_j, etc... I'm trying to pull only the characters to the right of the underscore. I played around with the below formula that I found under a similar question thread. But, it doesn't quite work. As it is now, it returns ecca_rt when I just want rt. I'm sure this is probably really close =IF(ISERROR( FIND("_",A1)),A1,RIGHT(A1,FIND("_",A1)-1))

View 3 Replies View RelatedMy logical test is =IF("D2"="E2",1,2). When I autofill or copy down, the row numbers do not change to "D3"="E3", etc. How do I delineate text and also have the ability for autofill or copy to change the rows as it goes?

View 2 Replies View RelatedBasically I have a column (lets call it column A) whereby I manually fill the cells green once I have received some documents, another column which has a numeric value in it (column B) and I want to create a third column which basically just copies column B but ONLY if column A is filled with a colour (actual colour doesn't matter cause I only use green)

I tried using the IF function but I don't know how to use cell colour as the logical test

I'm trying to create a formula that will determine the bonus (%) for sales made. Here's the table:

<10000 sales = 0% bonus

>=10000 sales but <=29999 sales = 5% bonus

>=30000 sales but <=to 44999 = 7% bonus

>50000 sales = 10% bonus

Since the sales and bonus figures are subject to change each year I'd like the formula to point to the numbers but that part seems easy if I can get the formula right.

I'm trying to use the following formula to verify if the "," is within a cell. Something is not right, because even for True conditions, I'm getting a False return. I think my True logical test is not written right.

View 3 Replies View RelatedI need a formula that will return a range of values from a list.

Example: I need a formula in cell A2 that looks at the list and will return all numbers that are >99 but 199 but299 but

excel 2007. Here is the situation:

I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:

A

B

C

D

[Code].....

So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.

The formula I have at the moment is this:

{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}

How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?

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 RelatedI have to decide if the final answer will be yes or no based on a combination of 4-5 cell values from a single row.

I have attached the details in the excel sheet.

I need a function that will use a column of text values and test these values

to see if one or more of the values exist in a single cell. If it does I need

the function to return true or false.

Ie. cell A1 contains the text "Jim Smith" the B column contains the test

names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".

Because Jim is in the cell A1 I would need the function in C1 to return the

value "true". If A1 contained the text "bob smith" then function in C1 would

return the value "false".

I'm trying to use the following IF formula:

=IF(J2>$J$143 and J2<$J$144,"1","0")

But it doesn't work - I obviously have the formula wrong. The problem arises with the inequality. how to have two conditions within the "logic test".

I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.

A1 represent a number of years and B1 represent gains or losses (negative)in dolars.

The way I see the logic is as follows:

If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)

If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.

On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.

I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:

If A1 = 0 then C1 = NGL

If B1 = 0 then C1 = NGL

If A1 >= 1 and B1 > 0 the C1 = LTG.

If A1 >= 1 and B1 < 0 the C1 = LTL.

If A1 < 1 and B1 > 0 then C1 = STG

If A1 < 1 and B1 < 0 then C1 = STL

I am trying to use an OR in a VBA IF statement. The condtition finds one vale but not the other. Due to the nature of the data, a value is marked either on, off, or both.

I have two seaches in the macro: one looks for on and both, and runs a sub, the other looks for off and both and runs a different sub. However I can't get the both case to run as it should.

If (Cells(i, CurCol).Value = "On") Or (Cells(i, CurCol).Value = "Both") Then

Cells(i, CurCol).Select

I have a spreadsheet wherein I've got a list of names, some of which say (See XXX) where XXX is a number. I've extracted the number to column 2. In column 3 I have another reference number. I essentially want to use column 3's numbers only if there is no value in column 2 (i.e. where there's no "See XXX in the list of names). When there is no value in column 2 I have #VALUE! - but I can't seem to use it in a logical statement. How can I do so? Or is there an easier way to pick between 2 cells?

View 4 Replies View Relatedi have problem with a long code.

So I divided that code in 4 parts.

So I have

module1 -> Sub Macro1()

module2 -> Sub Macro2()

module3 -> Sub Macro3()

module4 -> Sub Macro4()

I have declared

Option Explicit

Dim mdNextTime1 As Double

Dim myEnter As Integer

'Dim myK1 As Integer

'Dim myK2 As Integer

'Dim myK3 As Integer

Dim myK4 As Integer

Const myC As Integer = 82

Dim Lastrow As Integer

Dim myActualRow As Integer

all my sub have the following structure:

I'm trying to meet set criteria in b5 using AND the first works if both conditions are met in two cells but bur won't on one cell?

Private Sub PTIColours_Click()

If Range("b5") > 0 And Range("c5") < 0.149 Then

Range("a5").Interior.ColorIndex = 54

End If

End Sub

But this dosen't

Private Sub PTIColours_Click()

If Range("b5") > 0 And Range("b5") < 0.149 Then

Range("a5").Interior.ColorIndex = 54

End If

End Sub

I have tried searching for a good example of how to use logical operators on Mr Excel however the search is frustrated by "Small Words" in the search field ie IF, AND, NOT ,OR are too short to search.

why the following code within a macro does not work

Cells(x,11).Value = If(Or(Cells(x,136)="", Cells(x,134)="","",Cells(x,136)-Cells(x,134))

In column N I have a list of dates. In column Z I want to display either "Yes" where the date in column N has already passed or "No" if the date in column N has not passed. The formula I'm using in column Z is =IF(N2<TODAY(),"Y","N") however it just gives me "No" on every row regardless of whether date is passed or not.

View 11 Replies View RelatedI need a solution for this situtaion! Iam using a french version of excel2003!

I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!

I am working on a homework assignment, the books method was crap so I did my own method. I am almost done except for 1 last thing. I cannot find any function that will allow for 3 logical tests. I have a screen shot here explaining what I need to happen.

View 2 Replies View RelatedI inserted the following logical tests for cells E5 and E6. I saved the workbook with SE(B) in cell G3.

View 3 Replies View Relatedi had a query regarding a report i have to provide daily. i want the result to be in the form of a number which i pick up from another file.

now there are three colums in this file. one shows the date (i.e. in the ddmmyyyy format, e.g. 5/11/2008). another shows a region (e.g. SOUTHD) and the third shows a number (e.g. 445) of which i have to count how many instances occur for a particular day.

Hence for any given cell in which i would place this formula, i would need the number of times the number 445 occurred from the third column provided the date from the date column was, say, 5th November 2008 and the region was showing as SOUTHD.

pretty sure the COUNTIF function can be used to make such a formula when combined with some logical functions but am unsure on how to do so.

How to find whether a particular cell is blank or is text in a given row/column?

To elaborate more, I have some data in a row:

A B C D E

15 N 16 17 18

I am using a below formula to find whether there is any text in the row

ISTEXT(row(A1:E1)) but I am getting result as False.

How we represent AND OR in a function how many logical sequence can 1 if take

example =if((w4>u4 and v4=0) or (t4>r4 and s4=0);"yes";"no") can excel(2003) accept this , or there is an error in it what is error?

View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.

projects.xlsx

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