=Avg([ListPrice])-Avg([ClosePrice])/Avg([ClosePrice])

[sharkchaser]

In a group footer section of a report I have this: =Avg([ListPrice])-Avg([ClosePrice])/Avg([ClosePrice])

I use something very similar in Excel and it works perfectly. Excel Sample: =(E3-F3)/(F3) where E & F fields are currency.

The format is set as a percent.

Two problems.

First the calculation isn't producing the correct result when more than one record is averaged for the group.

Second is the result is #########.##%.

I'm looking for ##.##% but there seems to be no way in Access 2003 to place this format ##.##% in a report field.

Any ideas would be greatly appreciated.

Thanks Rick

 

[genomon]

You are missing parens in the calculation - try this:
=( Avg([ListPrice])-Avg([ClosePrice]) )/Avg([ClosePrice])

As far as formatting, in the text box's Format property, try entering ##.00%

Beir bua agus beannacht!

 

[sharkchaser]

Thanks genomon.

I inserted the ( but an error came back too many parenthesis.

I then made other adjustments based on your suggestion but to no avail. Still resulted in #########.##%

The format ##.00% made no difference either.

Thanks.

Rick

 

[dhookom]

Genomon's suggestion did not have too many parenthesis. Did you copy and paste or try to type it in?

What section of your report contains the expression? If this is the Page Footer, it won't work. Also, if the name of the text box is the same as the name of a field, it won't work.

What else have you tried? Did you try to break it down like
=Avg([ListPrice])
or
=Avg([ClosePrice])

Duane
Hook'D on Access
MS Access MVP

 

[sharkchaser]

The calculated fields are in the Group Section of the report just under the detail section.

No, I failed to transpose it correctly. Once I repaired it DOES work! How kewl! Thanks.

However, I'm not able to have the percentage formatted to show ##.##%

This is kind of important.

Any ideas?

 

[dhookom]

Just set the format property to Percent and the decimal to 2.

Duane
Hook'D on Access
MS Access MVP

 

[sharkchaser]

That did it.

It wouldn't work with the prior #########.##% attempt of formatting but once I corrected my mistake with Genomon's suggestion the percent control format worked perfectly.

Thanks much to both of you!

Rick

 

[sharkchaser]

Why would this work in the Detail Records section of the report and NOT in the RERPORT FOOTER section?

=([AvgOfClosePrice]-[AvgOfListPrice])/([AvgOfListPrice])

I'm stymied!

Thanks.

Rick

 

[dhookom]

I can't see anything wrong with your display. As a matter of fact I can't see your display ;-)

I can't see what you have tried to figure this out yourself. I expect this is truly in the Report Footer and not in the Page Footer.

Duane
Hook'D on Access
MS Access MVP

 

[sharkchaser]

I didn't reference the Page Footer.

It's in BOTH the Detail Records AND in the REPORT FOOTER.

It works well in the detail records but fails in the REPORT FOOTER.

Thanks Rick

 

[dhookom]

I still can't see anything wrong. If you want some help, you need to tell us a lot more about what you can see.

Duane
Hook'D on Access
MS Access MVP

 

[sharkchaser]

The Answer is . . .

=(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice])

Silly me!

From dbForums.

Thanks

Rick

 

[sharkchaser]

=(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice])

This didn't make a difference after all I'm sorry to say. It produces -3.69%.

The correct answer should be -29.36% - the difference between $732,750 and $517,610, $215,140. It's far more than -3.69%!

Please see the attached Percent Change.pdf file showing page 10 of 10 of the report.

Thanks.

Rick

 

[genomon]

There doesn't seem to be anything "attached" to your attachment - try clicking on it

Beir bua agus beannacht!

 

[sharkchaser]

I know.

I can't seem to find how to upload a one page .pdf file.

Attachments ask for a URL address?

Thanks

Rick

 

[genomon]

They aren't attachments, they're links. Try putting it on Picasa or some other free online host.

Beir bua agus beannacht!

 

[dhookom]

Sharkchaser,
Have you tried troubleshooting this by just creating three text boxes with control sources of:
[tt][blue]
=SUM([AvgOfClosePrice])
=SUM([AvgOfListPrice])
=(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice])
[/blue][/tt]
This should give you an idea of your root values.

Duane
Hook'D on Access
MS Access MVP

 

[sharkchaser]

Interesting . . .

=SUM([AvgOfClosePrice]) This is a ([field]) on my report.
=SUM([AvgOfListPrice]) This is a ([field]) on my report

=(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice]) This is a textbox on my report!

I'll try it . . . wait one . . .

Produces the same incorrect result: -3.69%

I'm thinking here . . . we're asking to Sum an AVG field which is already averaged. Why do we need to SUM the average field? How can you Sum a single field that is already averaged, or why would you?

This is very perplexing.

Rick

 

[dhookom]

You need to get an better understanding of your data and how to perform calculations. You really can Average Averages to get decent results.

You might be able to figure out what you need by pasting some records from your record source into Excel and then playing with it until you figure out what you need.

Duane
Hook'D on Access
MS Access MVP