converting excel equation into access equation

[gjh]

Hi

Im having difficulty converting an excel equation into access
The following is the equation in excel

=IF(SUM(IF(I24:I43="Yes",1),IF(I24:I43="No",1))>0,AVERAGE(IF(I24:I43="Yes",1),IF(I24:I43="No",0)),"N/R")

I have tried several different type of equation but with one luck.
I hope someone can help me out with this long equation
Thanks in advanced!

 

[CameronM]

did you try IIF? Syntax IIF(expression, truepart, falsepart). These can be nested too just like your Excel equation. Example IIF(expression, IIF(expression, truepart, falsepart),falsepart)

 

[gjh]

I have create the formula in access
average is equals to:

=(IIf([s2q1]="Yes",1,0)+IIf([s2q2]="Yes",1,0)+IIf([s2q3]="Yes",1,0)+IIf([s2q4]="Yes",1,0)+IIf([s2q5]="Yes",1,0)+IIf([s2q6]="Yes",1,0)+IIf([s2q7]="Yes",1,0)+IIf([s2q8]="Yes",1,0)+IIf([s2q9]="Yes",1,0)+IIf([s2q10]="Yes",1,0)+IIf([s2q11]="Yes",1,0)+IIf([s2q12]="Yes",1,0)+IIf([s2q13]="Yes",1,0))/(IIf(IsNull([s2q1]),0,1)+IIf(IsNull([s2q2]),0,1)+IIf(IsNull([s2q3]),0,1)+IIf(IsNull([s2q4]),0,1)+IIf(IsNull([s2q5]),0,1)+IIf(IsNull([s2q6]),0,1)+IIf(IsNull([s2q7]),0,1)+IIf(IsNull([s2q8]),0,1)+IIf(IsNull([s2q9]),0,1)+IIf(IsNull([s2q10]),0,1)+IIf(IsNull([s2q11]),0,1)+IIf(IsNull([s2q12]),0,1)+IIf(IsNull([s2q13]),0,1))

this is a very long formula, does anyone know if the formula can be made smaller?

2nd question is if nothing is selected for the questions, it's showing me #Num!
the reason it's showing me "#Num!" is because 0 is dividing by 0.
So, can someone tell me how to treat an empty result as "N/A"
Thanks!

 

[CameronM]

Not sure if you can make it smaller, without really understanding what it is you are calculating with the equation.

Is there no way to embed your IFF's into a query first and then use the result of the query?

For question 2: You could add a 'wrapper' IIF(expression=0,1,expression) to the divisor section of your code, since 0 divided by 1 is still 0 and this doesn't break Access.

 

[DCrake]

To get around the dive by zero error then in your nz() element replace 0 with 1 as 1 / 1 = 1 and n/1 = n

David

 

[gjh]

Hi CameronM and DCrake

i have 13 questions, range from S2Q1 to S2Q13. There are 3 optional answers for each question and they are "Yes", "No" and blank field (which means N/A).

so, im trying to calculate the average of S2Q1 to S2Q13, and the equation is in post 3.

everytime i select an answer for each question, i want it to do the calculation on the form first then when i save the total record, it should go into the db.

im not sure if the total score will appear on the form everytime i select an answer if i embeded the equation in the query!

as for question 2's answer, i have tried many ways to enter the equation but have no luck with it.

im very new to ms access and not familiar with the syntax.
if you can show me a simple example about how to use the nz function or how to write the syntax that would be great!

 

[DCrake]

For a start I would turn this into a function, and call the function on the after update of each control. Secondly you If statements only checks for one condtion... If Yes 1 else 0. according to your brief you are using tripple state. True,False,Blank. Also how do you average non numeric values?

Lets say the results of the 13 questions are

6 Yes
4 No
3 Blank

What would the average be? To me it would read "on average the user answered yes to most of the questions..."

Or if you are determining the highest count then calculating 13/6 as a % to give you the result "this person answered yes to 55% of the questions..."

David

 

[DCrake]

I have also copied your formula into excel to test the results and all I get is #Value

David

 

[gjh]

Hi David

that's the thing! i dont want to average the blank fields.

refering back to the example you gave

6 Yes
4 No
3 Blank

the answer to the above result is:

6/(6+4) = 0.6

i dont want to include any question that has a blank answer cause it's not applicable. otherwise, the result is not accurate anymore! if you know what i mean!

 

[gjh]

not sure why it's doing that but the formula is definately working in my excel!

 

[gjh]

not sure why it's not working but it's definately working in my excel

 

[DCrake]

Its a little bit longwinded but here's a possible solution

Create a function called MyAverages() and pass the results of the 13 questions to it.

Public Function MyAverages(Optional Q1 As Integer, Optional Q2 As Integer,....) As double

Dim YesCnt As Integer
Dim NoCnt As Integer

YesCnt = 0
NoCnt = 0

'Evaluate each question in turn

If Q1 = "Yes" Then
   YesCnt = 1
Else If Q1 = "No" Then
   NoCnt = 1
Endif

If Q2 = "Yes" Then
   YesCnt = YesCnt + 1
Else If Q1 = "No" Then
   NoCnt =  NoCnt + 1
Endif

'......

MyAverage = YesCnt/(YesCnt+NoCnt)

End Function

Next in your query add a column

ShowAverage:MyAverage([Q1],[Q2],[...])


The optional part of the arguments should handle and blank or null controls.


David