By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Average value & minimum & maximum values

P: n/a
In order to give a meaning average value and minimum and maximum values, I
would like to have a formula for a group of data after taking out those
extremes. Can someone share your way to accomplish it.

Thanks,

Scott
Nov 24 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Scott" <No*************@GMail.comwrote in
news:4s*************@mid.individual.net:
In order to give a meaning average value and minimum and
maximum values, I would like to have a formula for a group of
data after taking out those extremes. Can someone share your
way to accomplish it.

Thanks,

Scott
I wrote some code that takes the data set, sorts on the field of
interest, determines the total number of records, calculates the
median value, the 10th and 90th percentile, and the average of
those records that falls between the 10th and 90th percentile
values.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 24 '06 #2

P: n/a
Bob,

It sounds great. Could you please share your code with me.

Scott

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn*********************@66.150.105.47...
"Scott" <No*************@GMail.comwrote in
news:4s*************@mid.individual.net:
>In order to give a meaning average value and minimum and
maximum values, I would like to have a formula for a group of
data after taking out those extremes. Can someone share your
way to accomplish it.

Thanks,

Scott
I wrote some code that takes the data set, sorts on the field of
interest, determines the total number of records, calculates the
median value, the 10th and 90th percentile, and the average of
those records that falls between the 10th and 90th percentile
values.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 25 '06 #3

P: n/a
"Scott" <No*************@GMail.comwrote in
news:4s*************@mid.individual.net:
Bob,

It sounds great. Could you please share your code with me.

Scott
My boss would not be happy about that, since it's very focused
on the business, but there's enough code around the web to open
a recordset and scan through the rows.

here's some generic code to point you in the right direction

fFilteredAverage()
dim sSQL as string
dim rs1 as dao.recordset
dim lnumRows as long
Dim lUpperBound as long
Dim lLowerBound as long
dim dValue as double.

sSQL = "SELECT ..... ORDER BY ..." ' you will have to create the
right query for your use here.

set rs1 = currentdb.openrecordset(sSQL)
rs1.movelast
lnumrows = rs1.recordcount
lUpperBound = lnumrows\ 10
lLowerBound = lupperbound*9

rs1.absoluteposition = llowerbound - 1
do until rs1.absoluteposition = lUpperBound
'roll up the value. change fieldname below.
dValue = dvalue + !fieldname
rs.moveprevious
loop
ffilteredAverage = dValue /lnumrows *.8
end function
>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn*********************@66.150.105.47...
>"Scott" <No*************@GMail.comwrote in
news:4s*************@mid.individual.net:
>>In order to give a meaning average value and minimum and
maximum values, I would like to have a formula for a group
of
>>data after taking out those extremes. Can someone share
your
>>way to accomplish it.

Thanks,

Scott
I wrote some code that takes the data set, sorts on the field
of
>interest, determines the total number of records, calculates
the
>median value, the 10th and 90th percentile, and the average
of
>those records that falls between the 10th and 90th percentile
values.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com




--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 25 '06 #4

P: n/a
Bob,

Thanks for your useful guidance.

Scott

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Scott" <No*************@GMail.comwrote in
news:4s*************@mid.individual.net:
>Bob,

It sounds great. Could you please share your code with me.

Scott

My boss would not be happy about that, since it's very focused
on the business, but there's enough code around the web to open
a recordset and scan through the rows.

here's some generic code to point you in the right direction

fFilteredAverage()
dim sSQL as string
dim rs1 as dao.recordset
dim lnumRows as long
Dim lUpperBound as long
Dim lLowerBound as long
dim dValue as double.

sSQL = "SELECT ..... ORDER BY ..." ' you will have to create the
right query for your use here.

set rs1 = currentdb.openrecordset(sSQL)
rs1.movelast
lnumrows = rs1.recordcount
lUpperBound = lnumrows\ 10
lLowerBound = lupperbound*9

rs1.absoluteposition = llowerbound - 1
do until rs1.absoluteposition = lUpperBound
'roll up the value. change fieldname below.
dValue = dvalue + !fieldname
rs.moveprevious
loop
ffilteredAverage = dValue /lnumrows *.8
end function
>>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn*********************@66.150.105.47...
>>"Scott" <No*************@GMail.comwrote in
news:4s*************@mid.individual.net:

In order to give a meaning average value and minimum and
maximum values, I would like to have a formula for a group
of
>>>data after taking out those extremes. Can someone share
your
>>>way to accomplish it.

Thanks,

Scott

I wrote some code that takes the data set, sorts on the field
of
>>interest, determines the total number of records, calculates
the
>>median value, the 10th and 90th percentile, and the average
of
>>those records that falls between the 10th and 90th percentile
values.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 26 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.