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
View 9 Replies
ADVERTISEMENT
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
Dec 23, 2008
I have a spreadsheet which matches Reference numbers in Column A against Column B and vice versa; and assigns unique or duplicate values. I would like to be able to count the number of duplicates and uniques giving a total of how many appear in the spreadsheet. I've attached the spreadsheet to give you an idea of what its like.
View 3 Replies
View Related
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.
View 9 Replies
View Related
Mar 29, 2007
Probably missing something obvious here but I'm trying to determine the number of different results in column B where the citeria in column A is the same. For example, for the attached table the result for V00013 would be 4.
View 5 Replies
View Related
Jun 26, 2009
I'm having trouble getting a formula to sum the last 10 results of data entries, so far I have:
=SUM(OFFSET(INDEX(D2:D300,COUNT(D2:D300)),-9,,10,1))
This works fine on the assumption that all the rows contain a value 0 or 1, this may not be the case as rows may be left blank for a varying degree of time and results may be posted intermittently?
View 4 Replies
View Related
May 13, 2014
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
View 4 Replies
View Related
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
Oct 2, 2013
I'm trying to find a way to take a data set and write an excel equation/s to find out how many times in that column of data a certain result (number or letter) occurs consecutively for more than 5 (hoping that this is also customizable) times. For example....
DATE
USER A
1/1/2013
NO
[Code]....
Above are two columns, one with the date and another with the data I'd like to search through. So I'm hoping that I can write an equation/s that tells me how many times a certain value, in this case I'm looking for "No" occurs more than 5 times consecutively in the line of data. For example, for this particular data set, the final answer would be 2. There are only two instances where 5 or more cells with a "No" value follow each other.
View 3 Replies
View Related
May 28, 2014
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
View 7 Replies
View Related
May 9, 2006
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
View 9 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
Jun 27, 2013
From a large worksheet I'm trying to find out if a particular value is used in that formula. E.g. formula: (In cell C1)=A1+B1*.75
I want to check if .75 is used in the formula (which is 75%) There are 1000's of record would it be possible to check the entire column C
View 6 Replies
View Related
May 11, 2007
I'm having an intelligence lapse and I need help with a formula
Simply need to return a "1" if value in Cell X is equal to or greater than -(minus)4% and a "0" if value is less than -(minus)4%.
View 9 Replies
View Related
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
View 9 Replies
View Related
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.
View 3 Replies
View Related
Apr 11, 2008
I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100
Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.
The name of each student appears in CL2:CL100.
I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.
View 9 Replies
View Related
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: ......
View 10 Replies
View Related
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
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
Feb 3, 2014
I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls
View 1 Replies
View Related
Aug 26, 2009
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
View 7 Replies
View Related
May 8, 2008
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
View 6 Replies
View Related
Mar 12, 2009
I know the PROPER function will convert all text to capitalise each word, is there a formula that can convert only the first letter to caps and the rest to lower case?
View 4 Replies
View Related
Mar 3, 2008
is there a way to change the case of a cell/column without having to use a formula, i.e. just like you would in Word?
The formula seems to be a huge pain and I have to do lots of quick case changes in a large document.
View 9 Replies
View Related
Sep 21, 2006
What I'm trying to do is use SELECT CASE to do conditional formatting on a range of cells that I've named "ContactDate" (this range covers cells J3 to J42). I need to loop through the ContactDate range and test the current cell (which is in date format) against the cell in the adjacent column to the left (which is also in date format). The cell and font colours are to change based on the number of days difference. Below is the VBA code that I'm using. When I run it, it doesn't do anything to the cells. When I step through the code, however, and test it using the Immediate window it appears to work.
Sub ConditionalFormat()
Dim CurrentCell As Range
For Each CurrentCell In Range("ContactDate")
Select Case CurrentCell
Case CurrentCell - CurrentCell.Offset(0, -1) <= 1
CurrentCell.Interior.ColorIndex = 3 ' Changes background to red
CurrentCell.Font.ColorIndex = 2 'Changes font to white
Case CurrentCell - CurrentCell.Offset(0, -1) = 2
CurrentCell.Interior.ColorIndex = 5 'Changes background to blue
CurrentCell.Font.ColorIndex = 2 'Changes font to white
Case CurrentCell - CurrentCell.Offset(0, -1) = 3
CurrentCell.Interior.ColorIndex = 16 'Changes background to grey
CurrentCell.Font.ColorIndex = 2 'Changes font to white
Case CurrentCell - CurrentCell.Offset(0, -1) >= 4
CurrentCell.Interior.ColorIndex = 10 'Changes background to forest green
CurrentCell.Font.ColorIndex = 2 'Changes font to white
Case Else
CurrentCell.Interior.ColorIndex = xlNone 'Changes background to none
CurrentCell.Font.ColorIndex = xlAutomatic 'Changes font to automatic
End Select
Next CurrentCell
End Sub
View 7 Replies
View Related
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.
View 3 Replies
View Related
Mar 19, 2014
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
View 2 Replies
View Related
Jun 3, 2009
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
View 9 Replies
View Related
Apr 22, 2009
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies
View Related
