How to calculate average of two values

[SONCH]

Hi! I have a table of test scores as follows

student	Test1	Test2	Average
A	10		10
B		10	10
C	10	6	8
D	-	10	10
E	-		-
F	-	-	-
G

I need an expression that can satisfy the conditions indicated in the table above e.g.
1) if student A has 10 in Test1 and the teacher did not indicate any mark for test2, access should maintain that score as the average score.


2)if student D has "-" in Test1 and 10 test2 access should maintain 10 as the average score
3) if student F has "-" in both Tests access should maintain "-"
etc

[CJ_London]

your table is confusing, it apparently stores numbers and -. The latter is text and you cannot mix text and numbers. Implication is this is formatting. So you need to clarify the underlying data - remove the formatting and repost

also your table is not normalised - trying to do anything like this in an unnormalised table is extremely difficult, if not impossible. But if you want to give it a go, you will need multiple iif statements to test each column for its value - twice - once to determine the total value and once to determine the divisor.

[SONCH]

OK. this is what I mean.
consider the following:

field 1	field 2	average
10

In the Average field if you use the expression: Average: ([field1] + [field2])/2, no results will appear under the average field except a figure is in field 2.
OK what I want is an expression using the IIF function to condition access to allow the figure under field 1 to appear in the average field.
i.e. once this function is set and a figure in placed under field 1, the result looks as follows:

field 1	field 2	average
10		10

if both fields contain numbers the average should be calculated normally i.e.

field 1	field 2	average
10	15	12.5

[ArviLaanemets]

What Ajax tried to explain was, that currently you have 2 tests. When you (or we) provide a formula which calculates an average, and then afterwards you get 3rd test too, you have to create a new formula to calculate an average. And when 4th test will be added, again a new formula, etc. And at some moment you can't continue in this way, as Access limitations don't allow this.

[CJ_London]

you really want to change the way you do things but for just two columns the formula would be


(nz(field1,0)+nz(field2,0))/(iif(isnull(field1),0,1)+iif(isnull(field2),0,1))

properly structured you would just have

avg(field)

and as I suggested and Avril has expanded on if you add another test, you need to modify the calculation. Properly structured, you don't.