Syntax errot

[Ed]

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

 

[paul.robinson]

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

 

[Rick Rothstein]

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

 

[Ed]

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


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

 

[Ed]

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

 

[Rick Rothstein]

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

 

[Ed]

The worksheet formula appoach is interesting.

How could the "#VALUE " result be corrected?

 

[Ed]

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

Thanks again,


Ed English

 

[paul.robinson]

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