Conditional AVG in report footing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pyrahna - 15 Jun 2007 19:17 GMT

I am trying to do a conditional avg in my report footing. I would
like to avg values that are on the same row as an item between 7000
and 9000. for instance

6000 2
7500 3
8000 4
8500 5
9500 6
the answer would be 4
I have tried both of the following to no avail
=IIf(([RPM]>7000) And ([RPM]<9000),Avg([rpm]),0)
and
SUM(iif(([RPM]>7000) And ([RPM]<9000),[RPM],0))/SUM(iif(([RPM]>7000)
And ([RPM]<9000),1,0))
neither of which have worked.

any ideas would be appreciated.

Reply to this Message

KARL DEWEY - 15 Jun 2007 19:39 GMT

What is the name of the second field?

You did not include it in your compatation but I thought it was the field to
be averaged.

Signature

KARL DEWEY
Build a little - Test a little

> I am trying to do a conditional avg in my report footing. I would
> like to avg values that are on the same row as an item between 7000
[quoted text clipped - 14 lines]
>
> any ideas would be appreciated.

Reply to this Message

pyrahna - 15 Jun 2007 19:44 GMT

On Jun 15, 2:39 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> What is the name of the second field?
>
[quoted text clipped - 22 lines]
>
> > any ideas would be appreciated.

Sorry, the first field i am avg is the RPM field, the next field would
work like this(if this code actually worked like i would like it to)
=IIf(([RPM]>7000) And ([RPM]<9000),Avg([Power]),0)

or

SUM(iif(([RPM]>7000) And ([RPM]<9000),[Power],0))/SUM(iif(([RPM]>7000)

> > And ([RPM]<9000),1,0))

Reply to this Message

KARL DEWEY - 15 Jun 2007 20:58 GMT

Try this --
SELECT Avg(YourTable.RPM) AS AvgOfRPM, Avg(YourTable.Power) AS AvgOfPower
FROM YourTable
WHERE (((YourTable.RPM) Between 7000 And 9000));

Signature

KARL DEWEY
Build a little - Test a little

> On Jun 15, 2:39 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 33 lines]
> SUM(iif(([RPM]>7000) And ([RPM]<9000),[Power],0))/SUM(iif(([RPM]>7000)
> > > And ([RPM]<9000),1,0))

Reply to this Message