Average if

[Nyanko]

I'm looking to do a report based on average prices. So far I have:

Month .... Average Price .... Min Price .... Max Price

I'd like to add three more totals using an "if" function, hmmm let me explain.

"B" Average .... "F" Average ... "LP" Average
(Where B,F and LP are in one column called Type)

For the Average price field I've used : =Avg([Price]) what formula would I use to return an average only if the Type column equals a chosen type.

I'm not explaining this well. A picture paints a thousand words :

 

[Pat Hartman]

You would need to use a domain function:

for "B"
=DAvg("Price", "YourTable", "SomeField = 'B' AND YourDate = " & [YourDate])

for "F"
=DAvg("Price", "YourTable", "SomeField = 'F' AND YourDate = " & [YourDate])

for "LP"
=DAvg("Price", "YourTable", "SomeField = 'LP' AND YourDate = " & [YourDate])

 

[Nyanko]

Not sure I matched up my fields correctly :

=DAvg("Mgn/Pax","tbl_7day_tracker","Code = 'B' AND Departure Date = " & [Departure Date])

where

Mgn/pax = the price to average
Tbl_7day_tracker = the table with all the info
Code = B, F or LP
Departure Date = the date grouped by

I tried the formula and it came up with #error.

 

[Pat Hartman]

Your column names include spaces and special characters, very bad practice You need to be careful to ALWAYS enclose them in square brackets:

=DAvg("[Mgn/Pax]","[tbl_7day_tracker]","Code = 'B' AND [Departure Date] = " & [Departure Date])