Can't Autofill If Function With Text In Logical Test

Jun 18, 2014

My 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


ADVERTISEMENT

Logical Function: Logical Test With Three Possible Results

Apr 8, 2009

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.

View 4 Replies View Related

IF Function Using Cell Colour As Logical Test?

Aug 22, 2014

Basically 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

View 3 Replies View Related

Excel 2007 :: Index And Match Function With Logical Test

Feb 28, 2013

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?

View 3 Replies View Related

Under What Conditions Does A Logical Test

Feb 7, 2010

Under what conditions does a simple logical test, like

View 5 Replies View Related

Using Vlookup As The Logical Test

Sep 16, 2008

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.

View 9 Replies View Related

If Logical Test Multiple Criteria

Feb 17, 2012

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 Related

Logical Test For Date Ranges?

Oct 8, 2012

I 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

View 7 Replies View Related

Logical Test For Special Characters

May 17, 2007

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 Related

Formula Structure For IF / THEN With Logical Test Value Ranges

Feb 19, 2014

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.

View 3 Replies View Related

Confirm True In Logical Test Using Find

Nov 9, 2009

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 Related

Return Multiple Values From A List That Fit Logical Test?

Nov 15, 2013

I 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

View 6 Replies View Related

Function Allows For 3 Logical Tests

Oct 11, 2008

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 Related

AND OR IF Function For Logical Sequence

Dec 26, 2007

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 4 Replies View Related

Offset Formula - Logical Function Using OR

Jul 10, 2013

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.

View 2 Replies View Related

Workdays Function & Logical IF OR Formula

Aug 8, 2007

what was wrong with this formula, an error message comes up saying VALUE, im trying to get the formula to say that - If 2 dates are different then subtract one from the other to get a number of working days, but if the dates are both the same then to -1 from the value as for some reason the value is coming back as 2 when it should be 0?

=IF(OR(M17>0,N17>0),NETWORKDAYS(M17,N17,holidays)+1+Q17,IF(OR(M17=N17),NETWORKDAYS(M17,N17,holidays)-1,0))

View 9 Replies View Related

Basic IF Function With Multiple Logical Tests

Jul 10, 2014

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

View 7 Replies View Related

Macro For Copy / Paste With Logical Function

Mar 13, 2013

I am working with some data that consists of two types of messages: one containing vessel speed and another containing vessel type. Both messages share a common ship identifier number. What I'd like to do is run through column A (ship ID), check column B (ship type), and if column b has a value, to paste that value in all instances of that ship ID.

View 1 Replies View Related

IF Function: COUNT Script In The Logical Part Of The IF Statement

Jun 14, 2009

i am using the IF script, and i am also using the COUNT script in the logical part of the IF statement. i need to make sure that both K3 and V3 have a value inside of the cell in order for the statement to work, although when just K3 has a value, the IF - TRUE statement works anyway. any ideas on how to fix this? =IF(COUNT(K3,V3),SUM(K3+V3),"")

View 4 Replies View Related

Change The Color Of A Cell As The Result Of A Logical Function

Sep 25, 2009

How do I change the color of a cell as the result of a logical function?
For example: =IF(A1<A2,A1 is yellow, A2 is yellow).

View 2 Replies View Related

Function To Test For Whole Numbers

Jan 22, 2009

I have a resolution calculator that I am working on.

You put your original resolution in say 1440x1080

then below you put in any one of the new target resolutions.

Place 720 in the height and you get a message that says

"960 is your new matching Width resolution"

Id like to follow up on that with an if statment that test to see if the cell is blank if it is then null, if not then test 960 in this case to see if 960/16 = a non decimal number.

So in the cell to the right of that sentence it would return the result
"and 960 is 16 pixel safe" something like that.

I can probably figure out how I will handled the cell arrangement, numbers, and text I just for now need to find a way to do a test on whole numbers and return a text value (my guess is an if statement)

View 6 Replies View Related

Logical Formula With Output Based On The Text Input

Dec 2, 2009

Sometime itís so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isnít . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then itís should equal to the value in the box C7. Here is the formula I wrote that doesnít work.

View 4 Replies View Related

Array Function Test Not Working

Aug 7, 2009

Ok, I'm sure that there is something really obvious here, but I'm just not seeing it....

Test Data starting at cell A1:

View 14 Replies View Related

If Function To Test Dropdown Menu Selection?

Jun 11, 2014

I am developing a spreadsheet at work that is very similar to one that I made at a previous job. I am using the same formula in each spread sheet, but the one I am developing now does not work, where the previous one works perfectly.

I have a drop down menu created with data validation. Each possible selection is one or two letters of text, or the selection can be left blank. The drop down is cell A12, and an example of the if function that is used many times in this spreadsheet is as follows:

=IF($A$12="L",1,0)

In the previous spreadsheet, the function returns true only when the appropriate selection is made. In the latest version, it will return true if the appropriate selection is made, or if drop down selection is blank. I have been able to work around this with the following:

=IF($A$12="",0,IF($A$12="L",1,0))

View 6 Replies View Related

Cell Adjacency Test With CountIf Function

Feb 28, 2008

I'm trying to write a macro to test whether a cell has any neighbouring cells that match it's value.

I'd like this to be cumulative so that the more matches, the higher the value.

I'd then like the result to be written in another cell to set up a separate grid.

So far I'm getting error messages and I don't know why:

View 9 Replies View Related

Multiple A Logical Result Array With A Text Array And Return Text

Aug 6, 2009

I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.

View 3 Replies View Related

Test Two Cells And Set A Third Cell With Different Values Depending On Test Results

Aug 21, 2013

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

View 3 Replies View Related

Test For Existence Of Text In Range

Mar 10, 2009

I d like to test for the existence of text in range D5 to D10000 for example

I may have this 49000897890VGN21SP. VGN21SP is text so I d like to spot it

it may also exist on its own VGN21SP can you suggest me with a macro?

like this one

Sub DeleteNAs()
Dim C As Range
For Each C In ActiveCell.CurrentRegion

If C.Text = "#N/A" Then C.ClearContents

Next C
End Sub

View 9 Replies View Related

Autofill In Macro: Get A Function (sum) Of Several Cells To Be Automated In A Monthly Spreadsheet

Feb 10, 2009

I am trying to get a function (sum) of several cells to be automated in a monthly spreadsheet. I can get the function to work, but how do I get it to Autofil? The syntex seems to call for a range, which will be different every month. I can't figure out how to loop it, so I thought autofill would work.

View 3 Replies View Related

Formula That Will Test Text Conditions In A Single Cell

Apr 1, 2006

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".

View 10 Replies View Related







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