Syntax errot
 

Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = "fail" else a13 = "pass"

Thank you

Syntax errot
 

Hi
As a worksheet formula in cell a13
=if (and(min("a5:a9")< average("a5:a9")*0.95 , max("a5:a9") average
("a5:a9")*1.05) , "fail" , "pass")

As VBA
if min(Range("a5:a9"))< worksheetfunctions.average(Range("a5:a9"))
*0.95 and max(Range("a5:a9")) worksheetfunctions.average(Range
("a5:a9"))* 1.05
then Range("a13").value = "fail" else Range("a13").value = "pass"

the VBA should all be on one line


regards
Paul



On Mar 31, 3:08*pm, Ed wrote:
Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = *"fail" else a13 = "pass"

Thank you

Syntax errot
 

See if this does what you want...

With WorksheetFunction
If (.Min("a5:a9") < .Average("a5:a9") * 0.95) And _
(.Max("a5:a9") .Average("a5:a9") * 1.05) Then
a13 = "fail"
Else
a13 = "pass"
End If
End With

--
Rick (MVP - Excel)


"Ed" wrote in message
...
Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = "fail" else a13 = "pass"

Thank you

Syntax errot
 

Paul's suggestions
When I insert the worksheet formula I get "#value", although there
appears that I'm working only with numbers.

When I try to run the VBA all on one line I get the Compile error -
"sub or function not defined".

---------------------------------------------------------------------

Rick's suggestions

When I try to run his suggestion I get the message:
Unable to get the minimum property of the worksheet function class.
class


-----------------------------------------------------------------------------

On Tue, 31 Mar 2009 07:39:25 -0700 (PDT),
wrote:

Hi
As a worksheet formula in cell a13
=if (and(min("a5:a9")< average("a5:a9")*0.95 , max("a5:a9") average
("a5:a9")*1.05) , "fail" , "pass")

As VBA
if min(Range("a5:a9"))< worksheetfunctions.average(Range("a5:a9"))
*0.95 and max(Range("a5:a9")) worksheetfunctions.average(Range
("a5:a9"))* 1.05
then Range("a13").value = "fail" else Range("a13").value = "pass"

the VBA should all be on one line


regards
Paul



On Mar 31, 3:08*pm, Ed wrote:
Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = *"fail" else a13 = "pass"

Thank you

Syntax errot
 

Paul's suggestions
When I insert the worksheet formula I get "#value", although there
appears that I'm working only with numbers.

When I try to run the VBA all on one line I get the Compile error -
"sub or function not defined".

---------------------------------------------------------------------

Rick's suggestions

When I try to run his suggestion I get the message:
Unable to get the minimum property of the worksheet function class.
class


On Tue, 31 Mar 2009 10:43:49 -0400, "Rick Rothstein"
wrote:

See if this does what you want...

With WorksheetFunction
If (.Min("a5:a9") < .Average("a5:a9") * 0.95) And _
(.Max("a5:a9") .Average("a5:a9") * 1.05) Then
a13 = "fail"
Else
a13 = "pass"
End If
End With

Syntax errot
 

Sorry, I forgot the "Range" part...

With Application.WorksheetFunction
If (.Min(Range("A5:A9")) < .Average(Range("A5:A9")) * 0.95) And _
(.Max(Range("A5:A9")) .Average(Range("A5:A9")) * 1.05) Then
a13 = "fail"
Else
a13 = "pass"
End If
End With

--
Rick (MVP - Excel)


"Ed" wrote in message
...
Paul's suggestions
When I insert the worksheet formula I get "#value", although there
appears that I'm working only with numbers.

When I try to run the VBA all on one line I get the Compile error -
"sub or function not defined".

---------------------------------------------------------------------

Rick's suggestions

When I try to run his suggestion I get the message:
Unable to get the minimum property of the worksheet function class.
class


On Tue, 31 Mar 2009 10:43:49 -0400, "Rick Rothstein"
wrote:

See if this does what you want...

With WorksheetFunction
If (.Min("a5:a9") < .Average("a5:a9") * 0.95) And _
(.Max("a5:a9") .Average("a5:a9") * 1.05) Then
a13 = "fail"
Else
a13 = "pass"
End If
End With

Syntax errot
 

Ran revised code with no error message. Based on numbers in file
result would be "pass", but no result at all is showing in A13.

"and" in code should be "or".





On Tue, 31 Mar 2009 14:34:01 -0400, "Rick Rothstein"
wrote:

Sorry, I forgot the "Range" part...

With Application.WorksheetFunction
If (.Min(Range("A5:A9")) < .Average(Range("A5:A9")) * 0.95) And _
(.Max(Range("A5:A9")) .Average(Range("A5:A9")) * 1.05) Then
a13 = "fail"
Else
a13 = "pass"
End If
End With

Syntax errot
 

Is A13 the name of a variable? Or is it a cell address? If a cell address,
then the assignment lines should look like this...

Range("A13").Value = "fail"

and similar for the "pass" assignment line also. As for "and" versus "or"...
I just used the logical operator you showed in your original posting (only
you know what kind of test you are performing and what the results of that
test mean).

--
Rick (MVP - Excel)


"Ed" wrote in message
...

Ran revised code with no error message. Based on numbers in file
result would be "pass", but no result at all is showing in A13.

"and" in code should be "or".





On Tue, 31 Mar 2009 14:34:01 -0400, "Rick Rothstein"
wrote:

Sorry, I forgot the "Range" part...

With Application.WorksheetFunction
If (.Min(Range("A5:A9")) < .Average(Range("A5:A9")) * 0.95) And _
(.Max(Range("A5:A9")) .Average(Range("A5:A9")) * 1.05) Then
a13 = "fail"
Else
a13 = "pass"
End If
End With

Syntax errot
 

Success!! A13 is a cell address.

Thank you

The 'and' or was my error. I saw that only 'or' would give a correct
answer for all conditions.

Thanks again,


Ed English

On Tue, 31 Mar 2009 15:54:34 -0400, "Rick Rothstein"
wrote:

Is A13 the name of a variable? Or is it a cell address? If a cell address,
then the assignment lines should look like this...

Range("A13").Value = "fail"

and similar for the "pass" assignment line also. As for "and" versus "or"...
I just used the logical operator you showed in your original posting (only
you know what kind of test you are performing and what the results of that
test mean).

Syntax errot
 

The worksheet formula appoach is interesting.

How could the "#VALUE " result be corrected?



On Tue, 31 Mar 2009 07:39:25 -0700 (PDT),
wrote:

Hi
As a worksheet formula in cell a13
=if (and(min("a5:a9")< average("a5:a9")*0.95 , max("a5:a9") average
("a5:a9")*1.05) , "fail" , "pass")

As VBA
if min(Range("a5:a9"))< worksheetfunctions.average(Range("a5:a9"))
*0.95 and max(Range("a5:a9")) worksheetfunctions.average(Range
("a5:a9"))* 1.05
then Range("a13").value = "fail" else Range("a13").value = "pass"

the VBA should all be on one line


regards
Paul



On Mar 31, 3:08*pm, Ed wrote:
Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = *"fail" else a13 = "pass"

Thank you

Syntax errot
 

Thanks for the lead. Your worksheet formula works if the quotes are
reoved from the min, max, and avg values.

Thanks again,


Ed English



On Tue, 31 Mar 2009 07:39:25 -0700 (PDT),
wrote:

Hi
As a worksheet formula in cell a13
=if (and(min("a5:a9")< average("a5:a9")*0.95 , max("a5:a9") average
("a5:a9")*1.05) , "fail" , "pass")

As VBA
if min(Range("a5:a9"))< worksheetfunctions.average(Range("a5:a9"))
*0.95 and max(Range("a5:a9")) worksheetfunctions.average(Range
("a5:a9"))* 1.05
then Range("a13").value = "fail" else Range("a13").value = "pass"

the VBA should all be on one line


regards
Paul



On Mar 31, 3:08*pm, Ed wrote:
Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = *"fail" else a13 = "pass"

Thank you

Syntax errot
 

Hi
Sorry -you don't want the quotes "" in the formula.
regards
Paul

On Mar 31, 9:59*pm, Ed wrote:
The worksheet formula appoach is interesting.

How could the "#VALUE " result be corrected?

On Tue, 31 Mar 2009 07:39:25 -0700 (PDT),
wrote:

Hi
As a worksheet formula in cell a13
=if (and(min("a5:a9")< average("a5:a9")*0.95 , max("a5:a9") average
("a5:a9")*1.05) , "fail" , "pass")

As VBA
if min(Range("a5:a9"))< worksheetfunctions.average(Range("a5:a9"))
*0.95 and max(Range("a5:a9")) worksheetfunctions.average(Range
("a5:a9"))* 1.05
then Range("a13").value = *"fail" else Range("a13").value *= "pass"

the VBA should all be on one line

regards
Paul

On Mar 31, 3:08*pm, Ed wrote:
Please advise how to correct syntax error in the following:

if min(a5:a9)< avg(a5:a9)*0.95 and if max(a5:a9) avg(a5:a9)* 1.05
then a13 = *"fail" else a13 = "pass"

Thank you