# Logical Tests For Cells E5 And E6

Nov 4, 2008I inserted the following logical tests for cells E5 and E6. I saved the workbook with SE(B) in cell G3.

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

View 3 RepliesADVERTISEMENT

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 RelatedHow do you do two "if" statements?

I need to have two tests before i get a final result so i need to know the protocal to get two if statements.

Basically, I'm trying to calculate a pooled z-score from z-scores already stored in different cells.

Each individual z-score are calculated like this :

D1=IF(A1=999,999,((A1-10)/3)).

They are derived from standard scores with a mean of 10 and a SD of 3. I use 999 to mark missing values and this work flawlessly for single z-score cells.That way, if A1 is a missing value (999), Excel returns a value of 999.

Now here's my problem. How do I tell Excel to ignore 999 values if I am pooling multiple z-scores together?

Let's say A1, B1 and C1 are single z-scores. I want D1 to be the pooled z-score result, but I want it to ignore A1,B1 and/or C1 if their value is 999. For instance, if A1=+2.0 , B1 = 999 and C1= -1.50, I want my D1 cell to ignore B1 in the calculation.

I've tried : D1=IF(A1,B1,C1=999,999,(LC5+LI5+IK5)/3)) but it sure doesn't work...

I have a set of data in two columns, i.e. 1) dates and 2) names. Now I would like to post in the third column the name "x" if there is more than 2 years (or 730 days) between the current and the previus observation for name "x".

More specifically:

I would like the code/ function to always include the first "new" name then: 1) check whether subsequent names are equal, if equal; 2) check whether the difference between each obervation and the first "same" name is more than 730 days. The code should also always consider every observation with more than a 730 days difference as a "new beginning" for that specific name.

I have tried to use IF and OR functions but the function becomes too long and it does not quite solve the whole problem. Problems with my function occurs when the names change in column two. That is, my function is not able to distinguish between dates that belong to the same name and dates that belong to different names, when performing the second logical test.

I have attached a short example of my data. The problem is solved manually in the example file, just to give a better picture of what I am actually trying to do (I have a few thousand rows in my real file).

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.

I am working on a sheet to retrieve some cells which are not empty or equal to 0. Like..

A B

Item A

Item B 5

Item C 10

Item D

Item E 5

Item F

I want to retrieve only the cells which have got some values in column B. Like I want to get the list as follows

Item B 5

Item C 10

Item E 5

I'm trying to format a worksheet to show a one year due date with an alert 30 days out so that I can complete the task before the date due. I am using this for a preventative maintenance sheet for respiratory protection equipment that needs to be serviced annually. For instance; if I service a mask today the next service will be in one year. I'd like it to alert me 30 days out. If I could get that alert through outlook that would be fantastic.

View 5 Replies View RelatedI have a rather frustrating issue that dates a few months back, so I hope to solve it today. The following code tests a value in Column A. If the value is between 1-5 or equal to 11, column B will return the string A. It works.

View 3 Replies View RelatedI have a form in which a user pastes test names into a certain column of a spreadsheet. I have a listbox (not an ActiveX control) where I want the tests to be displayed. I want to resize the listbox depending on the number of tests pasted in-depending on where the last row is in this column of test names. Is there a way to do this automatically when the user pastes a new test set in the spreadsheet?

View 2 Replies View RelatedFormula that counts the number of people who are on my course and are taking their tests.

I have 5 classes, Class1, Class2, Class3, Class4 & Class5.

Column A details which class the pupil is in.

Column B details the pupils name.

Column C details the state of the course. This is either a DATE which they started, or "Paid" (they have paid for the course but not started), "Not Paid" (havent paid for course and havent started) or "In Progress" (Payment is in the process of being arranged).

Column D details the state of their final exam. This is either a DATE as to which they have finished and passed the exam, "Not Taken Yet" (Havent taken the exam yet) or "Fail" (They failed the exam).

This is where I am having problems, at the top of my sheet I want to summarise each classes statistics, but I am having problems with the COUNTIF. For example:

What formula can I use to add up the number of people in each class who have started the course?

E.g.

=countif(C10:C100," Is a Date ? ") AND is in Class1 ?

What forumla can I use to add up the number of people in each class who haven't passed the test ?

E.g.

=Countif(D10:D100,"Not Taken Yet" & "Failed") AND is in Class2 ?

I know I need an array to work this out.

And finally to really complicate things how about :

People in each CLASS who have STARTED the course (Date in C) AND haven't passed their test (Col D). I understand this is relying on a three part array whereas the others are 2 parts ?

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!

Under what conditions does a simple logical test, like

View 5 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.

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.

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

I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).

Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?

When using a IF formula, one answer is false.

Can the word FALSE be changed to a zero?

I am working between two worksheets. In one worksheet I have a list of

account numbers. In the second worksheet I have the same account numbers in

a different order. What I am trying to do is, If the account number from

worksheet one is found in the column with the account numbers in worksheet

two, I want to display the matching title for the account number that is in

the same row but different column in worksheet two.

So essentially (if acct # from column x in wks 1= an acct in number in

column m from wks 2, display the corresponding title in the same row of the

matching acct # found in column m). Is this possible?

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 following formula that returns #value!

=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),

COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))

Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.

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