Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quartile,3 (Excel2000)

    The enclosed attatchment is a small macro that doesn't produce the results intended.
    When it computes the Median the figures are correct. However, the Quartile numbers are a mystery.
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Quartile,3 (Excel2000)

    I can't help with Excel formulas, but I wonder: why not post an XLS workbook with sample data to manipulate with the code?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quartile,3 (Excel2000)

    1) You're formatting the results as whole numbers while the formulas may well return numbers with a fractional part.
    2) It appears that you are trying to calculate running "aggregrates" of columns H and K. What's the point of calculating the 3rd quartile of 2 numbers?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quartile,3 (Excel2000)

    Hans, enclosed a snippet of the worksheet.with additional columns to compute the medians and quartiles manually.
    The median function is the same for both (code/manual). The quartile results are incorrect as you can see. The question is WHY??
    Calculating the 3rd quartile of 2 numbers is just a starting point. the numbers lower in the column are the figures of interest
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quartile,3 (Excel2000)

    I don't understand your example. The macro you posted earlier produces formulas, not constant values. Could you attach an example that illustrates the use of the macro, not something you edited?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quartile,3 (Excel2000)

    Hans, all the cells that have a formula i.e. =a1+a2 show a constant instead of the formula in the formular bar.This is true for the entire worksheet. I don't know how this happened. Is there a way to trace back and correct this ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quartile,3 (Excel2000)

    I have no idea what you have done. Do you have a backup copy of the workbook?

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quartile,3 (Excel2000)

    If this macro was run, would that have caused the problem:
    Sub ConvertToValues()
    With Range("A1").CurrentRegion
    .Value = .Value
    End With
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quartile,3 (Excel2000)

    Yes, that macro converts ALL formulas in the region containing A1 to values.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quartile,3 (Excel2000)

    I found that macro in my stack of stuff and maybe ran it. Is there a procedure that can nullify that code and restore the formulas?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quartile,3 (Excel2000)

    No, the macro cannot be undone. Your only hope is a backup copy from before you ran the macro.

  12. #12
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Cape Cod
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quartile,3 (Excel2000)

    I can understand that, but no matter what workbook I open the formulas are converted.
    Does that mean that the computer is formatted?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quartile,3 (Excel2000)

    This has nothing to do with "the computer is formatted" as far as I know.
    Try Jan Karel Pieterse's <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post> to see if you can find the culprit.

Posting Permissions

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