Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: =(([ClosePrice])-([ListPrice]))/([ListPrice])

    Please see the .pdf attachment.

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Calculation Formula Reslults ? ? ?

    Please See Attached.

    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
    Attached Thumbnails Attached Thumbnails AggregateCalculationformul.jpg  

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The percent format operates on the assumption that the number 1 means 100%.

    What number are you coming up with before applying the "Percent" format?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Result Numbers

    See Attachment for Results.

    Thanks.
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    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. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What formula did you end up plugging in to Access that effectively gave you =B10?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    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
    Attached Files Attached Files

  12. #12
    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. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    And the Answer via TEK-TIPS is . . .

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

    This made it all work.

    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
  •