How to use this formula from Excel in Access?

[jset818]

I have this formula from Excel, =IF(AG2<>"",AVERAGE(AH2,AG2,X2),IF(AH2<>"",AVERAGE (AH2,AG2,X2),""))


I want to use that same formula for one of my queries in Access. Here's what I had in Access.

Average Availability: IIf([Source 2nd]<>"",Avg([Source 3rd],[Source 2nd],[Availability 1st Report Sent]),IIf([Source 3rd]<>"",Avg([Source 3rd],[Source 2nd],[Availability 1st Report Sent],"")))




That formula gives me an error that says The expression I entered has a function containing the wrong number of arguments.

What would be the correct way of implementing that same formula? Also what does <>"" mean? Usually when we use < or > it's followed by a number.

[JeffChr]

Try moving a paren at the end of your statement so it reads:

Avg([Source 3rd],[Source 2nd],[Availability 1st Report Sent]),""))

​<>"" means not equal to an empty string

[June7]

Avg() in Access is not the same as in Excel. It is an aggregate function. Aggregate functions are used to summarize records. Cannot use the intrinsic Avg() function to average several fields.

If you want to average data from 3 fields: ([field1] + [field2] + [field3]) / 3

Problem with that is if any field is null then the expression will return null.

"" denotes an empty string which is useless criteria with a number field because number fields cannot hold empty string and should cause error.

Multiple similar name fields indicates a non-normalized data structure.

[jset818]

Thanks. Using the ([field1] + [field2] + [field3]) / 3 worked great.

[jset818]

Hi June, using the ([field1] + [field2] + [field3]) / 3 worked but now every time I put >3 or >"3" in the criteria it would give me "Data type mismatch in criteria expression".

Average: ([3rd Sourcing Time]+[2nd Sourcing Time]+[Availability])/3

I'm able to use the criteria >"3" individually for those fields and even ([3rd Sourcing Time]+[2nd Sourcing Time]+[Availability]) but when I add the /3. It would say Data type mismatch in criteria. Any idea why?

[June7]

Provide sample data. Are these Date/Time type fields?

[jset818]

Provide sample data. Are these Date/Time type fields?

Each one of those fields uses the working days function like so,

3rd Sourcing Time: IIf(IsNull([Availability 3rd Report Sent]),"",IIf(IsNull([Source 3rd]),"",WorkingDays2([Source 3rd],[Availability 3rd Report Sent])))


The screenshot is using the Average: ([3rd Sourcing Time]+[2nd Sourcing Time]+[Availability])/3

[June7]

I am confused. The image shows calculation without error.

[jset818]

So the issue I was having was that I wasn't able to run the data if I put >"3" in the criteria. I was somehow able to resolve that issue, I don't know how I did it but it's pulling data just fine with the criteria. However, the data it's pulling are way off. Even the screenshot above, the values are actually incorrect. So a new issue has arisen. The ([3rd Sourcing Time]+[2nd Sourcing Time]+[Availability])/3 isn't pulling the right data. Each one of those fields have the correct calculations though. Is there another way to do an average of 3 working days functions?

[June7]

Filter criteria of >"3" doesn't work because the values are text. This means alpha sort rules apply - "10" comes before "3"

Calculating working days during a given period requires custom VBA function. You have that. I just don't understand why the results are being treated as text.

Going in circles. Unless you provide db for analysis, can't help further.

[jset818]

Quick question. The formula it uses in Excel, does it mean If AG2 is null, do average(AH2,AG2,X2), If AH2 is null, do average(AH2,AG2,X2), if not, then leave blank?

=IF(AG2<>"",AVERAGE(AH2,AG2,X2),IF(AH2<>"",AVERAGE (AH2,AG2,X2),""))

[June7]

The Average() calcs are the same for both conditions. Consider:

IF(OR(IsBlank(AG2),IsBlank(AH2)), "", Average(AH2,AG2,X2))

"" does not mean Null, it means empty string.