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

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

- Formula Help with Pass/Fail function
- Help with Pass / Fail Formula
- Help with a SUM function...
- Creating a dropdown list with Pass/Fail with colors...
- Need help with the sum function
- Please help with function in macro
- Help with countif/sumif function
- Help With The Upper Function
- Help with Date Functions
- Help with Sumif function with multiple crieteria
- Help with Forecasting function?
- Need help with replacing indirect function in a vlookup
- Help With The IF Function
- Need help with an Excel function for a Juniper firewall
- (SOLVED) Help with the IF function to calculate my ordinary hours for a timesheet
- Help with date formula/function
- Sending email with pass / fail result
- What am I doing wrong with the find function in vba?
- Help with Trim(string) function
- Help with Date/Time Function
- Help With SUMIF Fuction Please
- Help with the =COUNTIFS function
- Help with IF Excel Function
- Simple pass fail function in excel

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

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

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!

for instance:

Pass = Green

Fail = Red

Not run = Orange.

Any help is appreciate it. Thanks.

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 ]

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

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.

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

Trade",IF(ISERROR(VLOOKUP(H3780,Tivoli,4,FALSE))," ",MID(VLOOKUP(H3780,Tivoli,4,FALSE),1,15)))

Thanx

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!

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.

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?

Thanks for your help!!

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.

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.

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.

Can anyone please help me with this function,

I would greatly appreciate it!

natasha9794

I have attached my file!

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.

Thank you for your help.

Bill

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 & _ "Your particulars will be added" & _ " to the Brigade IST Database." 'You can add files also like this '.Attachments.Add ("C:test.txt") .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 '.Attachments.Add ("C:test.txt") .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

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).RowIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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

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

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

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

If grade >= 50 Then

passfail = ("Pass")

Else

If grade < 50 Then

passfail = ("Fail")

End If

End If

End Function Thank you for any help you can offer