Results 1 to 13 of 13
Thread: Quartile,3 (Excel2000)

20080811, 16:18 #1
 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.

20080811, 17:01 #2
 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?

20080811, 18:20 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20080812, 05:06 #4
 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

20080812, 07:16 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20080813, 07:10 #6
 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 ?

20080813, 07:15 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Quartile,3 (Excel2000)
I have no idea what you have done. Do you have a backup copy of the workbook?

20080813, 07:33 #8
 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

20080813, 07:55 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Quartile,3 (Excel2000)
Yes, that macro converts ALL formulas in the region containing A1 to values.

20080813, 07:58 #10
 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?

20080813, 08:03 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Quartile,3 (Excel2000)
No, the macro cannot be undone. Your only hope is a backup copy from before you ran the macro.

20080813, 08:07 #12
 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?

20080813, 08:11 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.