Average Formula in forms

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

Guest

I have a form with five subforms. Each subform contains four results. I need
a formula to average these results and display an average. I have already
tried a few formulas in text boxes on the form, however these havent worked.
Please Advise
Thanks in advance
 
How you do it depends on your data. You could (a) simply add the 5 subform
values, and divide by 5; (b) use DLookups on the individual subform sources
and DAvg the result; or (c) write a query (probably a UNION query) to do it
all in one go.

For example, if you have textboxes on the subforms, each containing one of
the values you want to average, the following might work, as the
ControlSource of a textbox on the main form:

=(Nz(Forms!frmMainForm!frmSubForm1.Form!Textbox1,0)+Nz(Forms!frmMainForm!frmSubForm1.Form!Textbox2,0)+Nz(Forms!frmMainForm!frmSubForm1.Form!Textbox3,0)+Nz(Forms!frmMainForm!frmSubForm1.Form!Textbox4,0)+Nz(Forms!frmMainForm!frmSubForm1.Form!Textbox5,0))/5

But even this'll error out of all 5 textboxes are Null or 0.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
 
Sorry, i forgot to add (didnt realise that it would affect it) that the
subforms are shown as datasheets. on my main form, i have added a text box
for each subform to enter number of tasks (results) and now i need a formula
to add up the results in the subform, and divide by the number of tasks.
sorry. thanks in advance
 
Then probably the most appropriate solution would be to run summation
queries for each of the subforms, and group them together in a union query.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
 
Back
Top