| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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). |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Syntax | Excel Programming | |||
| Run Time errot 1004, with formula | Excel Programming | |||
| If then syntax | Excel Worksheet Functions | |||
| SQL syntax | Excel Programming | |||
| errot in macro | Excel Discussion (Misc queries) | |||
Linear Mode
