Calculating an average on a form

[molsonexpert]

I have a form with 9 unbound text boxes, which are formatted as currency,
and default to the value $0.00. The ninth box calculates the average of the
other 8. However, I need it to calculate the average only for those boxes
which contain a value the user has entered. In other words, if values are
entered in boxes 2, 5 and 7, I need the calculation to be the sum of those
boxes divided by three. Which boxes contain values, and the total number of
boxes which contain values, will vary.

Thanks.

steve.

 

[John Vinson]

I have a form with 9 unbound text boxes, which are formatted as currency,
and default to the value $0.00. The ninth box calculates the average of the
other 8. However, I need it to calculate the average only for those boxes
which contain a value the user has entered. In other words, if values are
entered in boxes 2, 5 and 7, I need the calculation to be the sum of those
boxes divided by three. Which boxes contain values, and the total number of
boxes which contain values, will vary.

Thanks.

steve.

With a default of zero, and the possibility that the user might ENTER
a zero, I can't imagine any good way to do this.

You have seven numbers, four of them zero. Which zeros are the
deefaults, and which zeros were entered by the user?

If you can assume that zero is NEVER a valid value, then you can use
an expression like

-([txt1] + [txt2] + [txt3] + [txt4] + [txt5] + [txt6] + [txt7]) /
(([txt1] > 0) + ([txt2] > 0) + ([txt3] > 0) + ([txt4] > 0) + ([txt5] >
0) + ([txt6] > 0) + ([txt7] > 0))

The logical expressions in the denominator will evaluate to -1 if the
textbox contains a positive number; the - in front will convert the
(say) (50 + 12 + 0 + 0 + 55 + 3 + 0)/-4 to a positive result.

John W. Vinson[MVP]

 

[Al Camp]

John,
Again, I was lurking to see this answer...

([txt1] > 0) + ([txt2] > 0) + ([txt3] > 0)....

Using the True (-1) as a element counter?
I return your "Very Clever" remark to you... :-D
Al Camp

I have a form with 9 unbound text boxes, which are formatted as currency,
and default to the value $0.00. The ninth box calculates the average of
the
other 8. However, I need it to calculate the average only for those boxes
which contain a value the user has entered. In other words, if values are
entered in boxes 2, 5 and 7, I need the calculation to be the sum of those
boxes divided by three. Which boxes contain values, and the total number
of
boxes which contain values, will vary.

Thanks.

steve.

With a default of zero, and the possibility that the user might ENTER
a zero, I can't imagine any good way to do this.

You have seven numbers, four of them zero. Which zeros are the
deefaults, and which zeros were entered by the user?

If you can assume that zero is NEVER a valid value, then you can use
an expression like

-([txt1] + [txt2] + [txt3] + [txt4] + [txt5] + [txt6] + [txt7]) /
(([txt1] > 0) + ([txt2] > 0) + ([txt3] > 0) + ([txt4] > 0) + ([txt5] >
0) + ([txt6] > 0) + ([txt7] > 0))

The logical expressions in the denominator will evaluate to -1 if the
textbox contains a positive number; the - in front will convert the
(say) (50 + 12 + 0 + 0 + 55 + 3 + 0)/-4 to a positive result.

John W. Vinson[MVP]