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

    Unanswered: Formul works in Detail Records but fails in Report Footer

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

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

    I'm stymied!

    Thanks.

    Rick

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    =([AvgOfClosePrice]-[AvgOfListPrice])/([AvgOfListPrice])
    This refers to the current record in the detail.

    For your Report Footer you need to SUM the fields.
    =(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice])

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

    Percent Change Still Fails . . .

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

    This didn't make a difference 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
    Attached Files Attached Files

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

    Excel Example . . .

    Please see the .jpg Excel example attached.

    It's the same in Access 2003 but =(SUM([AvgOfClosePrice])-SUM([AvgOfListPrice]))/SUM([AvgOfListPrice]) is not working.

    I've tried so many variations on this but to no avail.

    Thanks.

    Rick
    Attached Thumbnails Attached Thumbnails Excel formula Example.jpg  

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Based on your PDF, the answer looks correct. Not sure where you're seeing 517,610, as the avg closing price on your report is 717,563. So,

    (717,563-732,732)/732,750

    is -2.07%, as displayed on the report. It does seem odd that the overall averages match the last detail entry. I can't match either of the overall averages using the amount totals and number of listings.
    Paul

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Having read your PDF I found that I gave you the incorrect information it is mathematically incorrect to sum averages and use this for calculations.

    I found errors in your Totals in the Report Footer -
    How can there be a total of 6033 listing when the actual total is 340, the same for your Listing and Closing amounts?

    Using the sample data in your PDF I have attached a database and the report as a jpg. The report totals are correct. In the design mode of the report called rpt_listing have a look at my totals calculations.
    Attached Thumbnails Attached Thumbnails Capture.JPG  
    Attached Files Attached Files

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

    Discovery From Excel DATA

    I queried the data then pasted it into Excel and the report footer sums are different from the Excel sums. Averages also.

    I need to look closer.

    I peeked at your report and don't see how you summed for each city in the detail records.

    I hope to look at all this evening.

    Thanks.

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Rick

    I did not sum for each city, I used your figures for each city in the PDF to produce the report. I assumed your city figures in the report are correct.

Posting Permissions

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