# 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

## Display Test String Depending On Date

Mar 31, 2009

I need a forumla that will dispay a set text message in a cell based on the date in another cell.

I need cell A1 to display the text message "Forwards Booking Date, No Action Required" Where cell A2 has a date entered in it that is the same as todays date or greater - i.e. in the future.

And if the date is in the past for no text message to appear.

## Test 2 Cell Values With IF AND Statement

Jan 26, 2008

I want to reference 2 cells to open a msgbox. Currently I use this formula in a cell "=IF(E4="C",IF(A4>30,"OT","")) I am trying to write a macro that will open a msgbox instead. This is what I've tried so far

Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1, 1).Address = "\$E\$4" Then
If Target(1, 2).Address = "\$A\$4" Then
If Target(1, 1) = "C" Then
If Target(1, 2) > "30" Then MsgBox("1")
End If
If Target(1, 1).Address = "\$E\$4" Then
If Target(1, 2).Address = "\$A\$4" Then
Target(1, 1) = "F" Then
Target(1,2) > "38" Then MsgBox("2")
End If

## Return Results Of Ping Test To Worksheet

Oct 18, 2012

I'm trying to work out some code that will take the contents of a cell (either alias or IP name), carry out a ping operation and return the result into the worksheet in the next columns to the right.

I've searched this site and found something similar that returns the result of the ping to a MSGBOX but this isn't quite what I want.

The endgame is to have a For/Next loop for a list of IP addresses in (say) A1:A10 and have the results pasted into columns B, C and so on, with each line of the ping result pasted into subsequent columns.

## Counting Test Case Results Using Formula

Nov 6, 2007

I have my test cases in below format and I would like to calculate # of test cases passed or failed using formula.

-------------------------------------------------
Test case #Step #Result
-------------------------------------------------
Test case 1Step 1pass
Test case 1Step 2pass
Test case 1Step 3fail
Test case 2Step 1pass
Test case 2Step 2pass
Test case 2Step 3pass
Test case 2Step 4pass
Test case 3Step 1fail
Test case 3Step 2fail

I need below result using formula:
# of test cases - Pass = 1
# of test cases - Fail = 2

## Test Cell Values Before Adding Weekdays

Jan 8, 2008

I have the followinf formula

=IF(A2="","",IF(C2>TODAY(),"",IF(E2="",IF(B2="1st",WORKDAY(A2,4,\$H\$1),WORKDAY(A2,5,\$H\$1)),C2)))

What I am trying to do is cause (where the formula is) to:

If A2 is empty, put nothing,
If C2 is greater than today put nothing, otherwise put C2
If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.

I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.

## Converting Cell Values To TEXT For Conditional Test

Apr 28, 2006

and then this is the logical check that runs anytime someone wants to run a macro...

End If

## If Formula To Test For 3 Values

Jan 25, 2007

I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.

The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.

I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.

=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")).
This produces a blank or error result.

=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,""))
This produces an error whatever is in the J column

## Test 2 Cells Before Using SUM

Nov 28, 2007

I am using the formula below to return a blank cell if either logical test returns true but apparently the OR operator returns a #VALUE error when applied to a non numeric value in a cell - in this case cell E40. =IF(OR(E40="N",P40=""),"", SUM(P40,S40,V40,Y40,AB40,AE4))+F40. My goal is to return a blank cell if E40 contains the letter N or P40 is blank, otherwise perform SUM(P40,S40,V40,Y40,AB40,AE4))+F40.

## Generate Test Data From Range Of Values Using Vba?

Apr 15, 2014

I want to randomize a range of values stored in sheet 1 and insert them in another cell.

I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50

the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.

## Excel 2007 :: Test For Existing Values

Nov 7, 2011

I have a workbook with several tabs. the first tab is a data input sheet: subtotals from this sheet get posted to cells on the other sheets. at the top of the Data sheet is a drop down validation list that the user will select the next month from. On each of the other sheets, is a grid with calendar months down the left hand side and years across the top:

Excel 2007BCDEFGHI26Incoming111009080727Month201220112010200920082007200628
JAN1201135317522050194029FEB1392150817082130217230MAR1538164117831823246831
APR1338132717301819211632MAY1485129615762008244933JUN14991380162618652028187034
JUL15491382166020362091170335AUG16531383148619252132197236SEP15901411155620051982214937
OCT23391324159420732014247738NOV1440146718842186282139DEC15631602203721472197Incoming

I am trying to figure out a simple method to check whether there is data on any of the four other sheets in the spot corresponding to the month selected by the user on the Data tab. the format of the validation list dates are dd/mm/yyyy in cell B2 on the data tab.

## Test Selection Values & Graph/Chart

Oct 19, 2007

The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,

B4 = 23
21-40 = 1

C4 = 30
21-40 = 2

D4 = 45
41-60 = 1

After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................

## Copy Test From Cells Into Row

Jul 9, 2014

I am trying to find a script where I can see information more clearly. I am trying to copy certain cells and have them into a single row. For example, everytime the word USER comes up, I want that in a single row and all the information that comes after it and below to be in the same row. So I would want all this information in a single row. And it would start a new row when the word USER comes up. I will attach an excel table for an example. Working progess.xlsm

LU*
USER=irrcertaNAME=CERTAUTHAnchorOWNER=irrcertaCREATED=01.097
DEFAULT-GROUP=PASSDATE=00.000PASS-INTERVAL=N/APHRASEDATE=N/A
ATTRIBUTES=REVOKED
REVOKEDATE=NONERESUMEDATE=NONE
LAST-ACCESS=UNKNOWN
CLASSAUTHORIZATIONS=NONE
NO-INSTALLATION-DATA

[Code]...

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

## UserForm_Initialize Code To Test Certain Cells

May 9, 2007

The below code tests a particular cell before displaying the userform. If Range ("Bal_BF") contains an error (such as "#N/a"), a message box is displayed and the form should NOT display.

However, the code is still trying to display the userform, and gives me a error message "Object variable or With Block variable not set".

When Bal_BF contains a value, the form displays correctly.

Private Sub UserForm_Initialize()

If Employee = "" Then Call Retrieve

If IsError(Range("Bal_BF")) Then
Call MsgBox("Your name doesn't appear in the Holiday & Absence file." & vbCr & vbCr & _

## Perform Conditional Test Based On Sum Row Of Cells Using VBA?

Feb 6, 2014

In the past I have used VBA script to change column with size based on cell values as per follows:

[Code]....

Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.

To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:

[Code] ....

I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b.

## If Not Equal To: Test If The Value Of Cells AZ2:AZ2000 Are MB50017

Jun 17, 2009

Collumn AZ 2:2000 should always have a value of MB5017 in the first 6 positions on the left, with an additional four digits following. I need to test if the value of cells AZ2:AZ2000 are MB50017 - leaving the variable last four digits. If not than replace them with "MB50017"

Column AZ

MB500174443
MB500174446
NB500176554
VB401038789

The first 5 characters should always be MB50017 plus the last four digits
The column should then look like:
MB500174443
MB500174446
MB500176544
MB500178789

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

## Lookup Results Depending Upon Cell Values

Aug 25, 2008

Depending upon a figure entered e.g. = \$ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.

## Test If A Cell Has #N/A

Nov 9, 2009

How do I test if a cell has #N/A?

## If Then Statements: Construct A Logic Test Using The Range Of Cells

Aug 2, 2006

How do you write an If then statement using a range of cells? I want to construct a logic test using the range of cells A7:A19. I want the logic test to see if the any of the values =1. If the test is true then I want it to display the value in B7:19 (which ever cell corrulates to the cell in column A that has the value of 1) and display the number in the B column.

## VBA UDF - Test If Cell Is Colored

Oct 10, 2012

I need a UDF to test a single cell if it is colored. If it is the result returned should be a 1, if not the result will be blank. This is what I have so far, but I don't know much about VBA.

Function Filled(MyCell As Range)
If MyCell.Interior.ColorIndex > 0 Then
Result = 1
Else: MyCell = ""
End If
End Function

## VBA Test To Determine If Cell Has A Formula Or Value

Dec 28, 2008

I need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.

## IF Then Statement To Test If A Cell Is Blank

Aug 19, 2009

How would I write an IF Then Statement to test if a cell is blank? (meaning it could have "Div/0!", text, 0, or "n/a".)

## Value Test Of A Range Of Cell And If Statement?

Feb 17, 2012

I am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .

Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long

[Code].....

## Formula To Test For Cell Color

Sep 10, 2008

Is there formula to use if I want an IF statement to add a date "TODAY()" to a cell if a corresponding cell is highlighted? I would search for this in the archives if I had a clue what to search for. "Conditional formatting", maybe?

In Col J "Appt. Date", I want to add today's date if the cell in Col C of that row is green.

How would that work? My worksheet is posted below: ......

## Test If A Cell Is Reference In An Array

May 4, 2006

I find myself needing this often and hope there is an elegant formula that can make this easier.

Is there a formula that will test if a particular cell is referenced somewhere in another array or vector? Specifically, I find that I have to aggregate long lists into categories to fit budget formats of various lenders and investors. For example, my detail budget has separate rows for Water, Sewer, Garbage, Electrical, and Gas. These expenses have to be aggregated on one funder’s budgets as “Utilities.” Sometimes after going through this I find that my totals don’t add up, i.e. I left an item out of the aggregated budget. I would like to be able to add a column on the detail budget to test if each budget item has been referenced in the aggregated budget.

## Test If Cell Is Empty Or Blank

Dec 16, 2006

I want to add an IF statement to my macro that will run only if a cell on the worksheet is not blank (empty). I know how to use the if statements but can't figure out how to test a cell for contents.

## Test If Cell Is Blank/Empty

Jul 10, 2007

I'm testing to see if a cell has nothing in it, and if that is true then it will execute the code. My problem is that even though the cell is blank the field does have a drop down list (list validation set for that field) below is the code i used, but doesn't work.

If Range("b3").Value = " " Then

## Test And Sum The Same Range Of Cells In Each Sheet By Simply Coping And Pasting The Formula

Feb 2, 2010

On Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:

=SUMPRODUCT((INDIRECT("'"&B2&"'!\$B\$3:\$B\$200")=\$A\$2)*(INDIRECT("'"&B2&"'!\$C\$3:\$C\$200")="Yes")*(INDIRE CT("'"&B2&"'!\$E\$3:\$E\$200")))

This gives me the intended result. B2 in this case is January. C2 is February and so forth.