Free Microsoft Excel 2013 Quick Reference

# Help with PASS/FAIL function

Hi all,

I need a formula that determines if a value passes or fails. basically, if cell g21 is ±15% of cell b7, then it passes. if it is more than ±15%, then it fails. I am at a loss for where to start. please help.

thanks,

ias

## Related Results

### Formula Help with Pass/Fail function

Hi all!

1st post, so I hope I got it in the right section :D

I'm having some problems getting my whole formula to be recognised by Excel.

What I have got is:

=IF(COUNT(E2),IF(SUM(E2)<=40.05,"Pass","Fail"),"enter data"

basically, telling the sheet to put a Pass/Fail if it meets/doesn't meet this criteria.

What I need to have also is a formula telling this formula that if it is a Pass under 10 seconds that it needs to say "Null" or "Invalid". I have tried entering this myself but it always shows up as 'my argument is too long'

Is there a way of telling the formula to do this or would I need to dip inside VB Editor (not my greatest skill) in Excel for this?

Any help greatly appreciated

### Help with Pass / Fail Formula

Hi

I would like help with a formula to do the following

If cell A is >= cell B or <= cell C then display the test "FAIL" and highlight the cell red otherwise display the text "PASS"

Try as I might I have been unable to make a formula to get this to work

### Help with a SUM function...

Hi everyone,

I need some help with a SUM function. Basicaly, in my spreadsheet, the first couple of rows have numbers and letters in them.

The problem is that i need every column to have a total, which is also located at the top.

Is is possible to get a SUM functions which, for exampls adds up column B from cell B4 to infinity? What i mean is that the end range is basicaly infinity, as i dont know how many rows down i will go.

If this is not possible, is it possible to get a SUM of column B, but somehow make it exclude the first 3 rows from its sum?

ANY HELP WILL BE GRATEFULLY APPRECIATED! THANK YOU ALL!

### Creating a dropdown list with Pass/Fail with colors...

I am going to use excel for test cases. I can creat a drop down list with the selections "pass", "fail", "not run". That isn't a problem using the data validation toolbar. However I would like to basically color the cell (test case) with a certain color based on the drop down selection.

for instance:

Pass = Green
Fail = Red
Not run = Orange.

Any help is appreciate it. Thanks.

### Need help with the sum function

Hi I am new to this forum and I've been racking my brain for the past couple hours on this. If anyone can help with hints or suggestions I really appreciate it.

A
1) 2
2) 1
3) 3
4) 1
Total 7

Here is my Simple excel spreadsheet.
The "total" line is set to =sum(A1:A4) But if I were to insert a line between Total and line 4 the Total line will not include the new line in its "summing".
Can anyone give me suggestions on how to make it so that the newly added line will be added to the sum function in total. I need to make a macro, but I can do that myself I just need to know how to do this. Thanks in advance.

[ This Message was edited by: jaxkewl on 2002-12-04 15:12 ]

[ This Message was edited by: jaxkewl on 2002-12-04 15:26 ]

Hi, I need help with this formula.

Function Sumartotales(todosumar As Object)
Application.Volatile
For Each cell In todosumar
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sumartotales = total
End Function

Dim suma1 As Variant, Totalventa As Integer, Totalpesos As Integer, Totalstock As Integer, ransuma1 As Range

Set ransuma1 = Range(Cells(2, 6), Cells(str2FILA, 6))
suma1 = Application.WorksheetFunction.Sumartotales(ransuma1)
Totalventa = suma1

If I use the formula in excel, It's OK, but when I put it in the Macto, don't function.
I don't know if I have to put the formula in the same page as the Application. I tested it in both parts and still don't function.

Thanks!!!

### Help with countif/sumif function

Hi

I am setting up a spread sheet and need some help with the countif/sumif function. I have the days of the month listed in columns. At the end I have a column “total days”. In this “total days” column I need to calculate the no. of days taken by each employee. So if:

If FD is entered in the range cells it should calculate as 1.0 day
If FH is entered in the range cells it should calculate as 0.5 day

Please can you help with a formula for this?

Thanks.

### Help With The Upper Function

Hello,

I need some help with using the UPPER function....in this formula I need to
convert the text "Global Trade" to upper case, where inside the formula do I
place the UPPER funtion?

=IF(IF(ISERROR(VLOOKUP(H3780,Tivoli,4,FALSE)),"",M ID(VLOOKUP(H3780,Tivoli,4,FALSE),1,15))="applicati ons","Global

Thanx

### Help with Date Functions

I need help with the following functions for dates. In Column A I have a
start date (including time) and in Column B I have the end date (including
time). I have been tasked with finding out if the end time is past 8:00 a.m.
each day. If the end time is past 8:00 a.m. I must place a â€śYâ€ť in Column C
or an â€śNâ€ť in Column C.
This out my data looks:

Column A Column B
Start Time End Time
10/20/2005 23:00 10/20/2005 23:53
10/20/2005 10:00 10/21/2005 8:30
How do I check the time with the date being in the beginning of this data?
The date maybe the same day or could be the next day.

Thanks!

### Help with Sumif function with multiple crieteria

I am new to the forum and need some help with a sumif function. I am attempting to take a date, see if it is in a list of dates, if so, return the sum of the values 60 days before and 60 days after that date in two separate columns. The date changes along with the data fields. Please help. Thanks.

### Help with Forecasting function?

Help with a forecast function.

I am trying to figure out how to calculate the number value to increase my
percentage from whatever it is currently to 95%. I have 3 pieces of data,
good records, nuetral records, and bad records. In cell 1a I have the % of
cells b1, c1, d1. Here is my calc:

=(b1/SUM(b1:d1)

a1 b1 c1 d1
87% 20 2 1

I am trying to figure out how many more of B1 I need to reach a goal of 95%
in cell a1. My answer in this case is 32 more, which I want to display in
cell e1.

How can I do this?

TIA,
Bob W.

### Need help with replacing indirect function in a vlookup

Hello,

I am using a VLOOKUP formula incorporating the INDIRECT function and want to replace the indirect function with something else, perhaps a match, index, choose, offset or some other function. I cannot figure out a way to write this formula with a different function and need help. Here is a link to my file.

The following formula in cell B7 is:

=VLOOKUP(C4,INDIRECT(N7),2)

Cell C4 is a value greater than 1.

The formula in cell N7 is:

=VLOOKUP(NUM,DATA,7)

NUM is cell C2 and can be a value greater than 1. The contents of range name DATA is

1 Asample D E F G AAA
2 Bsample D E F G BBB

This table can have up to 100 rows and 50 different values in column 7.

The formula in N7 therefore returns AAA or BBB by matching the value of NUM in range DATA and looking up the contents of column 7. AAA or BBB are range names of other tables which are used in cell B7 thanks to the INDIRECT function. Using this function B7 does a lookup in table AAA or BBB.

Table AAA looks like this

0 5.75%
100,000 4.75%
250,000 3.75%
500,000 2.00%
1,000,000 0.00%

Cell B7 will return a value equal to =LOOKUP(C4,BBB,2). If cell C4 is equal to 125,000 then the value returned in B7 is 4.75%. I cannot hardcode the range name of the table because the user can change the value of NUM.

Any suggestions?

### Help With The IF Function

Hi all,

I am looking for a little bit of help with a problem I am having. I am developing worksheet for the purpose of calculating Short Rate earned premiums. I have the majority of what I need figured out but I am haveing some trouble with the IF function. What I am trying for is something like:

IF(C4=D4,70,0)

But for some reason the fuction won't work for me with "=", i can get it to work with ">=" or "<=" but if I use "=" it continues to come up false even when its true. Any insight would certainly be appreciated. Thanks.

### Need help with an Excel function for a Juniper firewall

Hi,
I am not an extensive excel user which is why I am here. I need help with a function.

Here is an example of the data I have in A2
set service "XD Port 1" + udp src-port 0-65535 dst-port 138-138

I added the function =REPLACE(A2,13,1,"""source ") so I get

set service "source XD Port 1" + udp src-port 0-65535 dst-port 138-138

I would like to transpose the 0-65535 and the 138-138 so I end up with

set service "source XD Port 1" + udp src-port 138-138 dst-port 0-65535

All the lines I have look similar. The 0-65535 and the 138-138 (or whatever number) are always preceded by src-port and dst-port so I was trying to do a search for that text and then do a copy and paste or something like that.

Thanks for any help.

### (SOLVED) Help with the IF function to calculate my ordinary hours for a timesheet

Hello
Im am having trouble with the IF function in excel 2010, for a template of a pay sheet.
I have been asked to -Using an IF statement in cell G8, calculate the ordinary hours component of total hours.
Using a nested IF statement in H8, calculate the overtime hours.
Using a nested IF statement in I8, calculate the time and a half (T ˝) overtime hours
Using a nested IF statement in J8, calculate the double time hours.
The ordinary hours are 8hrs a day.
On weekdays, the first 2 overtime hours are paid at time and a half and remaining overtime hours are paid at double time rates.
Hours worked on Saturday are considered overtime, with the first 2 hours worked at time and a half rates and remaining overtime hours are paid at double time rates.

As you can see in my file i have just put the normal hours which is 7.30am - 4.00pm,with a 30 min lunch break, so i am not sure how to work out the IF functions.
I will be putting the employees actual hrs in my next spreadsheet, this will be the template i will use to calculate the info.

I would greatly appreciate it!
natasha9794

I have attached my file!

### Help with date formula/function

My accountant wants me to enter a date in a spread sheet called invoices. It can be done using the hot keys CTRL+;. However, I would like to automatically enter a date in a cell called 'Date Pd' when the invoice is paid. This seems like such a simple task but not being a programmer I must be missing something that this powerful program can do. An example follows;

Invoice # Date Amt Due Amt Pd Date Pd

27677 12/30/10 300.00 300.00 ??/??/??

=IF(amount paid>0, enter date in Date Paid cell

I have tried to used the =Today() and =Now() with the =If functions but these two will allow the date to be updated to the current date. Thanks for taking the time to read this post.

Bill

### Sending email with pass / fail result

Hello. I am still realitively new to using Macros in Excel and have been doing a lot of experimenting and researching, but am having difficulty getting the right code. When the test result is transferred from my Test Sheet to my Test Tracker sheet, it goes in column H3. The pass for the test is 60. So when the result is >59 I want "Passed" to be inserted in the J3 column. If the result is <60 then I want "Failed" to be inserted in the J3 column. Also, as soon as the test result and the Pass/Fail results are inserted, I have it programmed to send an email automatically for a pass or a fail. Right now I have to insert Passed or Failed into J3 manually before the test result is inserted, in order for the email to send out. I am using Excel 97-2003.

The codes are below.

```This is in my Instructors worksheet of
the Test Tracker Workbook

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("H3"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 59 Then
Call SendEmail
Else
If IsNumeric(Target.Value) And Target.Value < 60 Then
Call SendEmail
End If
End If
End If
End Sub

This in a Module of the Test Tracker Workbook

Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "J").Value) = "passed" Then

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "IST TEST RESULT"
.Body = "" & Cells(cell.Row, "B").Value _
& Cells(cell.Row, "C").Value _
& vbNewLine & vbNewLine & _
"You passed with  " & _
Cells(cell.Row, "H").Value _
& "%" & _
vbNewLine & vbNewLine & _
" to the Brigade IST Database."

'You can add files also like this
.Send  'Or use Display
End With
On Error GoTo 0
Cells(cell.Row, "J").Value = "sent acceptance e-mail"
Set OutMail = Nothing

Else
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "J").Value) = "failed" Then

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "IST TEST RESULT"
.Body = "" & Cells(cell.Row, "B").Value _
& Cells(cell.Row, "C").Value _
& vbNewLine & vbNewLine & _
"Your test result was  " & _
Cells(cell.Row, "H").Value _
& "%" & _
vbNewLine & vbNewLine & _
"You will need to do a re-test " & _
" Please let me know when you are ready for another test."
'You can add files also like this
.Send  'Or use Display
End With
On Error GoTo 0
Cells(cell.Row, "J").Value = "sent failed e-mail"
Set OutMail = Nothing

End If
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub```

### What am I doing wrong with the find function in vba?

Hello,

I need a bit of help with the .FIND function.
I am still a VBA newbie so I get stumped quite a bit.
I would like some help on why the last line of the code doesnt work.
I get the error message "Run Time Error '91', Obhject variable or With block variable not set"

I used this line on another macro similar to this one :
'AA = Sheets("Activities").Columns("A:A").Find(issuetype, , xlValues, xlWhole, SearchOrder:=xlByColumns).Row

and it works, so I dont understand why it doesnt work this time!
Any help would be greatly appreciated.
Thank you.

```
VB:
srcwbk = Workbooks("munzees")
Set TgtWbk = ThisWorkbook
Set srcws = srcwbk.Sheets("Munzees")
Set tgtws = TgtWbk.Sheets("munzee")

a = 2
b = 2

Do While srcws.Cells(a, 1).Value  ""
latitude = srcws.Cells(a, 1).Value
longitude = srcws.Cells(a, 2).Value
aa = tgtws.Columns(1).Find(latitude, , xlValues, xlWhole, SearchOrder:=xlByColumns).Row

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Help with Trim(string) function

I need help with trimming a string.

the original string is like this: Bob:100,200,300

I need a variable with the 100
I need a variable with the 200
I need a variable with the 300

I can figure out the 300, but not the others.

```Text =
Bob:100,200,300
LineValue1 = Trim(Right(Text, Len(Text) - InStr(1, Text, ":")))
LineValue2 = Trim(Right(LineValue1, Len(LineValue1) - InStr(1, LineValue1, ",")))
LineValue3 = Trim(Right(LineValue2, Len(LineValue2) - InStr(1, LineValue2, ",")))```
Thanks for any help.

Excel 2000

### Help with Date/Time Function

I am working with date/time functions trying to figure out how many
hours were spent doing a job. I have a start time and a finish time.

But I need to subtract out any time between five pm and six am and
weekends from the times. These would be times that no one was actually

working on the job. For example I have

4/5/2005 18:23 4/6/2005 17:04

Could someone give me a clue as to how this might be done?

Thanks,
Steve Monczka
smonczka@hotmail.com

### Help With SUMIF Fuction Please

Hi All,

New to the site but hoping to learn allot, please could someone help with the sumif function I have on a spreadsheet,

I am try do is a sumif that Excludes a column if it as 100% in there, I have managed to do the sumif (( =SUMIF(A:A,F3,E:E)+SUMIF(A:A,F3,F:F) )) but
I don’t know what to put if I want to ignore it, if it has 100% in a cell

I have attached a test sheet if someone could have a look and try point me in the right direction

It works out a percentage returned if column E / F = the full amount in column k

### Help with the =COUNTIFS function

I'm in QA and am trying to create a table for tracking build verification reports based on 3 criteria:

-Date (only accept entries within the past week)
-Conditional (for this example, I will count how many verification passed)
-Branch (the branch the build is based off of, for this example, branches will be “Primary” and “Tertiary”)

Through some help here, I found that the =countifs function works best, and I was able to count how many builds passed based upon the following formula:
=COUNTIFS('BVT History'!B2:B1000, ">="&TODAY()-6,'BVT History'!C2:C1000,"Pass")
Where the ‘B’ range is dates, and the ‘C’ range is conditionals.

I’m almost done with this table; however, needed to enter functionality where the user types in the name of the branch they want results for in cell “C2” and the range is A2:A100 on the BVT History workbook. So I figured the following formula would work:
=COUNTIFS('BVT History'!B2:B1000, ">="&TODAY()-6,'BVT History'!C2:C1000,"Pass", 'BVT History'!A2:A1000, "=C2")

However, it doesn’t. Do you know what I’m doing wrong here? I tried hard coding in conditionals as well, but it seems like the countifs function breaks when applying a third criteria field.

### Help with IF Excel Function

Hello guys,

I hope someone can help figure out what I'm doing wrong.

I want to fill a cell with one of these 4 options: ALL OK, FAIL, INCOMPLETE and NOT TESTED

The only acceptable contents range F17:F60 are "OK" and "NOK"

If all contents of the range F17:F60 is empty, then cell F12 = NOT TESTED
If all contents of the range F17:F60 contains OK, then cell F12 = ALL OK
If contents of the range F17:F60 contains at least 1 NOK, then cell F12 = FAIL
If contents of the range F17:F60 contains at least an empty, then cell F12 = INCOMPLETE

IF(COUNTA(F17:F60) =0, "NOT TESTED",(IF(COUNTIF(F17:F60,"OK"),"ALL PASS", (IF(COUNTIF(F17:60, "NOK"), "FAIL", "INCOMPLETE"))))

Thanks

### Simple pass fail function in excel

Hello everyone this is my first post in these forums hopefully you guys could offer me some advice I have never done programming before and we had to take it in my computers business functions course.

I have to create a simple function where the program takes a grade either 100 - 50 or 49 -0 the program will then output "pass" for the first option and "fail" for the second.

When I use the function it says #VALUE! I cant figure out what is wrong. I am using excel 2010.

Function passfail(grade As Double) As Double