calculating average on the form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a from with 10 control boxes, 8 with numbers, the
9th control box is the total of the 8 control boxes and a
10th control box is for the average. I am trying to
calculate the avearge value for the 8 control boxes? I am
encountering two problems

1. If any of control box is left null or empty then the to
total and the average boxes are also empty, null. I can
solve this problem by leaving a 0 in the control boxes but
then it leads to my second problem.

2. The 0 entries in the control boxes gets included in
calculating the average. So the average is wrong.

For example out of the 8 boxes I use 1 and enter 10 leave
the rest of the boxes with 0's. The total works fine it
gives me 10 but the average will be 1.25, when it should
be 10.

Any help would be so appreciated.



Cheers
 
There are several ways to deal with this:
1.Simple
For the total: = Iif(isnull([Text1], 0,[Text1]) + Iif
(isnull([Text2], 0,[Text2])+...+ Iif(isnull([Text8], 0,
[Text8])
For the avg: = [Total]/(Iif(isnull([Text1], 0,1]) + Iif
(isnull([Text2], 0,1)+...+ Iif(isnull([Text8], 0,1))

2.Neat (with VB code)
Use the On Change event of all eight input text boxes to
fire a piece of code something like the following:

Sub Calculate_Total_and_Average()
ttl = 0
avg = 0
cnt = 0
For i = 1 to 8
if not(isnull(Forms("FormName").Controls("Text" &
i))) then
ttl = ttl + Forms("FormName").Controls
("Text" & i)
cnt = cnt +1
end if
Next
Forms("FormName").Controls("Total") = ttl
Forms("FormName").Controls("Average") = ttl / cnt
End Sub

In both cases, you need to cahnge the form and control
names accordingly.
In the second case, you need to have the 8 input boxes
named all as nameX (name might be whatever), X being
consecutive or evenly stepped numbers, so the loop can
work (you will need to change the i range and possibly add
a step, if not 1 through 8).

HTH,
Nikos
 
Back
Top