1. Registered User
Join Date
Mar 2004
Location
California
Posts
502

The above formula is correct for all of the calculations except the three noted on the attachment.

They are the correct field names. (Not as shown on the field labels at the top of the report.)

Why would it fail on these three?

Thanks . . . Rick

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
I notice all of the numbers that are incorrect where for sections that had more than one listing. I also noticed you're not using aggregate functions in your formula. What does [ClosePrice] and [ListPrice] refer to when you have multiple listings?

3. Registered User
Join Date
Mar 2004
Location
California
Posts
502

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

Hi Teddy - after studying that again I figured it had something to do with multiples in the averages.

Aggregate functions in my formula?

Aggregate usage is something I've not been able to get a hand on in Access. Never!

How does that apply here in the formula?

Would it be something like . . . =((AVG[ClosePrice])-([AVGListPrice]))/([AVGListPrice]))) ?
Oh by the way - these fields under a group footer.

[ClosePrice] and [ListPrice] are fields in a table. the closed price is what the property sold for. The list price is the current asking price.

Rick

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Yup, if you intend to operate on averages then you need to explicitly tell Access that you would like to operate on them. Otherwise why wouldn't it choose sum, or count? When you don't specify an aggregate function like that, Access will assume you intended to operate on the last record of the group. If you run your formula against the list and sale prices of the very least record of each group, you'll see what I mean.

5. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Calculation Formula Reslults ? ? ?

The results from this formula are a set of numbers ########%, eight numbers ending with a percent sign.

The numbers do not reflect what the correct answer should be.

I'm looking for ##.##% or -##.##%

There are no errors flagged with the formula.

Any ideas?

Thanks Rick

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
The percent format operates on the assumption that the number 1 means 100&#37;.

What number are you coming up with before applying the "Percent" format?

7. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Result Numbers

See Attachment for Results.

Thanks.

8. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
You fed the average asking price into the formula and formatted as a percentage.

Those are all your average asking price multiplied by 100, which is what formatting as percentage is supposed to do.

Have you ever used excel, by chance?
Last edited by Teddy; 05-14-10 at 23:36.

9. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Excel. . .

I have that formula everywhere in Excel and it works flawlessly.

Example . . .

=(D10-B10)/B10

10. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
What formula did you end up plugging in to Access that effectively gave you =B10?

11. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Excel 2003 Example

See Attached

This is Excel's formula that accomplishes the same thing which is then formatted as a percentage.

Any ideas how this can be applied to Access 2003?

Thanks . . . Rick

12. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Still Fighting Percent Format

I have this in a TextBox

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

Percent
0

Returns ########.##

I need ##.##%

Any Ideas?

Thanks

13. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## And the Answer via TEK-TIPS is . . .

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

Hope it helps you.

Thanks.

Rick

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•