|
Help with ranking
Hi
Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
Help with ranking
The 2 values are probably not equal. There is probably a very small decimal
difference. If the values are decimals and are formatted to display in a certain way they may *appear* to be equal but really are not. For example: 10.025 10.031 If you *format* those values to *display* 2 decimal places: 10.03 10.03 They appear to be equal but the *displayed value* is not the true value. The true values are 10.025 and 10.031 which are the values RANK is calculating. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
Help with ranking
Hi T. Valko
Thank you for your input. Sorry but that's not possible. I put in the numbers: Jan Feb Diff Rank A 10,8 10,8 0,0 10 B 8,8 7,2 -1,6 15 C 11,9 11,4 -0,5 11 OK D 7,8 5,6 -2,2 16 E 11,6 10,0 -1,6 14 F 7,5 7,0 -0,5 11 OK G 8,2 9,6 1,4 5 H 8,5 11,2 2,7 1 I 13,0 13,6 0,6 8 J 6,0 7,8 1,8 3 !! K 4,6 7,2 2,6 2 L 9,4 11,2 1,8 4 !! M 10,6 11,5 0,9 7 N 13,7 15,0 1,3 6 O 9,3 9,5 0,2 9 P 11,1 9,8 -1,3 13 C and F is OK, but J and L should be rank 3 both. -- Stone "T. Valko" skrev: The 2 values are probably not equal. There is probably a very small decimal difference. If the values are decimals and are formatted to display in a certain way they may *appear* to be equal but really are not. For example: 10.025 10.031 If you *format* those values to *display* 2 decimal places: 10.03 10.03 They appear to be equal but the *displayed value* is not the true value. The true values are 10.025 and 10.031 which are the values RANK is calculating. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
Help with ranking
It's a rounding issue but I lack the technical expertise to explain it so
that you would understand. The error is so small that it can't be detected by formatting. For example: 7.8-6 = 1.8 So, if you enter this formula you would expect that the result should be TRUE but it's not, the result is FALSE: =(7.8-6)-1.8 = 0 = FALSE (1.8) - 1.8 = -2.2E-16 -2.2E-16 = 0 = FALSE In your posted sample data there are also duplicate -1.6 that have different ranks: B 8,8 7,2 -1,6 15 E 11,6 10,0 -1,6 14 If you round the diff column then you will get the ranks that you expect. With your Jan and Feb data in the range A2:B17... Use this as the diff formula entered in C2 and copied down to C17: =ROUND(B2-A2,1) See this article on rounding issues: http://www.cpearson.com/Excel/rounding.htm -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi T. Valko Thank you for your input. Sorry but that's not possible. I put in the numbers: Jan Feb Diff Rank A 10,8 10,8 0,0 10 B 8,8 7,2 -1,6 15 C 11,9 11,4 -0,5 11 OK D 7,8 5,6 -2,2 16 E 11,6 10,0 -1,6 14 F 7,5 7,0 -0,5 11 OK G 8,2 9,6 1,4 5 H 8,5 11,2 2,7 1 I 13,0 13,6 0,6 8 J 6,0 7,8 1,8 3 !! K 4,6 7,2 2,6 2 L 9,4 11,2 1,8 4 !! M 10,6 11,5 0,9 7 N 13,7 15,0 1,3 6 O 9,3 9,5 0,2 9 P 11,1 9,8 -1,3 13 C and F is OK, but J and L should be rank 3 both. -- Stone "T. Valko" skrev: The 2 values are probably not equal. There is probably a very small decimal difference. If the values are decimals and are formatted to display in a certain way they may *appear* to be equal but really are not. For example: 10.025 10.031 If you *format* those values to *display* 2 decimal places: 10.03 10.03 They appear to be equal but the *displayed value* is not the true value. The true values are 10.025 and 10.031 which are the values RANK is calculating. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
Help with ranking
Hi Valko
YES YES YES It realy helped. I do not understand why but it help. As I said why: 7.80000 - 6.00000 = 1.80000 11.20000 - 9.40000 = 1.80000 is NOT the same I realy do not understand. However your solution was briliant. So thank you very must here from Denmark. But I still think that there is an eror in microsoft's Excel as these figures are pretty accurate. But anyway THANK you again Stone -- Stone "T. Valko" skrev: It's a rounding issue but I lack the technical expertise to explain it so that you would understand. The error is so small that it can't be detected by formatting. For example: 7.8-6 = 1.8 So, if you enter this formula you would expect that the result should be TRUE but it's not, the result is FALSE: =(7.8-6)-1.8 = 0 = FALSE (1.8) - 1.8 = -2.2E-16 -2.2E-16 = 0 = FALSE In your posted sample data there are also duplicate -1.6 that have different ranks: B 8,8 7,2 -1,6 15 E 11,6 10,0 -1,6 14 If you round the diff column then you will get the ranks that you expect. With your Jan and Feb data in the range A2:B17... Use this as the diff formula entered in C2 and copied down to C17: =ROUND(B2-A2,1) See this article on rounding issues: http://www.cpearson.com/Excel/rounding.htm -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi T. Valko Thank you for your input. Sorry but that's not possible. I put in the numbers: Jan Feb Diff Rank A 10,8 10,8 0,0 10 B 8,8 7,2 -1,6 15 C 11,9 11,4 -0,5 11 OK D 7,8 5,6 -2,2 16 E 11,6 10,0 -1,6 14 F 7,5 7,0 -0,5 11 OK G 8,2 9,6 1,4 5 H 8,5 11,2 2,7 1 I 13,0 13,6 0,6 8 J 6,0 7,8 1,8 3 !! K 4,6 7,2 2,6 2 L 9,4 11,2 1,8 4 !! M 10,6 11,5 0,9 7 N 13,7 15,0 1,3 6 O 9,3 9,5 0,2 9 P 11,1 9,8 -1,3 13 C and F is OK, but J and L should be rank 3 both. -- Stone "T. Valko" skrev: The 2 values are probably not equal. There is probably a very small decimal difference. If the values are decimals and are formatted to display in a certain way they may *appear* to be equal but really are not. For example: 10.025 10.031 If you *format* those values to *display* 2 decimal places: 10.03 10.03 They appear to be equal but the *displayed value* is not the true value. The true values are 10.025 and 10.031 which are the values RANK is calculating. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
Help with ranking
You're welcome. Thanks for the feedback!
This kind of rounding problem is posted here often. Others can explain it much better than I can. It has to do with the computer converting binary numbers to decimal numbers. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Valko YES YES YES It realy helped. I do not understand why but it help. As I said why: 7.80000 - 6.00000 = 1.80000 11.20000 - 9.40000 = 1.80000 is NOT the same I realy do not understand. However your solution was briliant. So thank you very must here from Denmark. But I still think that there is an eror in microsoft's Excel as these figures are pretty accurate. But anyway THANK you again Stone -- Stone "T. Valko" skrev: It's a rounding issue but I lack the technical expertise to explain it so that you would understand. The error is so small that it can't be detected by formatting. For example: 7.8-6 = 1.8 So, if you enter this formula you would expect that the result should be TRUE but it's not, the result is FALSE: =(7.8-6)-1.8 = 0 = FALSE (1.8) - 1.8 = -2.2E-16 -2.2E-16 = 0 = FALSE In your posted sample data there are also duplicate -1.6 that have different ranks: B 8,8 7,2 -1,6 15 E 11,6 10,0 -1,6 14 If you round the diff column then you will get the ranks that you expect. With your Jan and Feb data in the range A2:B17... Use this as the diff formula entered in C2 and copied down to C17: =ROUND(B2-A2,1) See this article on rounding issues: http://www.cpearson.com/Excel/rounding.htm -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi T. Valko Thank you for your input. Sorry but that's not possible. I put in the numbers: Jan Feb Diff Rank A 10,8 10,8 0,0 10 B 8,8 7,2 -1,6 15 C 11,9 11,4 -0,5 11 OK D 7,8 5,6 -2,2 16 E 11,6 10,0 -1,6 14 F 7,5 7,0 -0,5 11 OK G 8,2 9,6 1,4 5 H 8,5 11,2 2,7 1 I 13,0 13,6 0,6 8 J 6,0 7,8 1,8 3 !! K 4,6 7,2 2,6 2 L 9,4 11,2 1,8 4 !! M 10,6 11,5 0,9 7 N 13,7 15,0 1,3 6 O 9,3 9,5 0,2 9 P 11,1 9,8 -1,3 13 C and F is OK, but J and L should be rank 3 both. -- Stone "T. Valko" skrev: The 2 values are probably not equal. There is probably a very small decimal difference. If the values are decimals and are formatted to display in a certain way they may *appear* to be equal but really are not. For example: 10.025 10.031 If you *format* those values to *display* 2 decimal places: 10.03 10.03 They appear to be equal but the *displayed value* is not the true value. The true values are 10.025 and 10.031 which are the values RANK is calculating. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
Help with ranking
You will understand the problem when you try to calculate the fixed point
binary exact representation of numbers like 7.8, 1.8, 11.2, and 9.4. [Hint: there isn't one.] -- David Biddulph "Stone" wrote in message ... Hi Valko YES YES YES It realy helped. I do not understand why but it help. As I said why: 7.80000 - 6.00000 = 1.80000 11.20000 - 9.40000 = 1.80000 is NOT the same I realy do not understand. However your solution was briliant. So thank you very must here from Denmark. But I still think that there is an eror in microsoft's Excel as these figures are pretty accurate. But anyway THANK you again Stone -- Stone "T. Valko" skrev: It's a rounding issue but I lack the technical expertise to explain it so that you would understand. The error is so small that it can't be detected by formatting. For example: 7.8-6 = 1.8 So, if you enter this formula you would expect that the result should be TRUE but it's not, the result is FALSE: =(7.8-6)-1.8 = 0 = FALSE (1.8) - 1.8 = -2.2E-16 -2.2E-16 = 0 = FALSE In your posted sample data there are also duplicate -1.6 that have different ranks: B 8,8 7,2 -1,6 15 E 11,6 10,0 -1,6 14 If you round the diff column then you will get the ranks that you expect. With your Jan and Feb data in the range A2:B17... Use this as the diff formula entered in C2 and copied down to C17: =ROUND(B2-A2,1) See this article on rounding issues: http://www.cpearson.com/Excel/rounding.htm -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi T. Valko Thank you for your input. Sorry but that's not possible. I put in the numbers: Jan Feb Diff Rank A 10,8 10,8 0,0 10 B 8,8 7,2 -1,6 15 C 11,9 11,4 -0,5 11 OK D 7,8 5,6 -2,2 16 E 11,6 10,0 -1,6 14 F 7,5 7,0 -0,5 11 OK G 8,2 9,6 1,4 5 H 8,5 11,2 2,7 1 I 13,0 13,6 0,6 8 J 6,0 7,8 1,8 3 !! K 4,6 7,2 2,6 2 L 9,4 11,2 1,8 4 !! M 10,6 11,5 0,9 7 N 13,7 15,0 1,3 6 O 9,3 9,5 0,2 9 P 11,1 9,8 -1,3 13 C and F is OK, but J and L should be rank 3 both. -- Stone "T. Valko" skrev: The 2 values are probably not equal. There is probably a very small decimal difference. If the values are decimals and are formatted to display in a certain way they may *appear* to be equal but really are not. For example: 10.025 10.031 If you *format* those values to *display* 2 decimal places: 10.03 10.03 They appear to be equal but the *displayed value* is not the true value. The true values are 10.025 and 10.031 which are the values RANK is calculating. -- Biff Microsoft Excel MVP "Stone" wrote in message ... Hi Using "RANK" should automatically rank eg. 10 observations from 1 to 10 and if 2 observations have the same value they should rank eg. 7 and 7 and the next rank would then be 9. I have made several collums and for som collums this is actually so. However in som collums the ranking for two equel values is eg 7 and 8. Therefor the same "rules" dos not aply for this collums. Can somone explane this to me or even better come up with an solution. PS: Sorry for my english (I am for Denmark) -- Stone |
| All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com