I think you are making a fundamental statistical/mathematical mistake in using the percentage figure. You cannot use it to make a meaningful comparison, especially if you are trying to do a comparison based on your letter symbols. You will not be able to get correct cross tab totals.

0

Many thanks, Bryco. I never used macro in Excell, so it's totaly new for me, but as soon as I understand (and read carefuly your explanations) I will try it. I downloaded the file so you can delete it from your site. Thanks. _____________ Wizard Fred, No, I made charts manualy and they look very nice.

0

A good looking chart is not necessarily accurate. I hope that you are not using the charts for any important scientiific or legal matter. I have worked for lawyers analyzing evidence and have many times found errors like the one that you are doing.

0

Fred, the instructions appear to be many many words but simply use it as you would any other function. The difference is that it is not part of Excel so you have to have it's worksheet active/open in order to access the function found under "User Defined" Using it is quite easy once you get the hang of it. Regards, Bryan

0

Bryco Sure. I didn't have the time today, but I will try it tomorrow certainly. Thanks a lot. ____________________ Wizard Fred What kind of mistake or misinterpretation could come out of this? If you count the % formula correctly the chart is accurate and reflect the reality.

0

Bryco, I tried the macro and it works! Many Thanks!!! I did a few tables already! I had to modify the formula because it didn't check occurances properly. It mix them. For each occurence you have to change the table lookup area. Someone else sent me a Vlookup formula but I didn't try it yet. It look pretty much the same.

0

I can not imagine what you mean by it Mixed them. The only thing that I have ever had to change was which occurance I wanted it to return. In your data there is no need to sort them. In mine I had to sort by date in order to get it to return the values in the correct order as they occurred. If you are having to change the lookup table then you are spending to much effort than is needed. If you set the table's parameters on the first one with the correct $$ references then you should not need to change them again. Refer to the formula as it is shown within the formular bar for the sample I made that contains your data. If you go accross the top row the only things that change in the formula is the occurance number. Either way, you got it working for you and that is good. Bryan

0

If I select all the datas range ie: $A$1:$C$19 for all the occurences, in the first result column I will have the values of the next occurence if the value of the curent occurence is zero (or not found in the first occurence). ie, if it doesn't find a letter symbol in the first list, it will look up in the next list and put the value in the same column, instead of puting the value of the second occurence in the second column.

0

I now understand but that is correct. It will find the first occurance not matter how far down your list it is located. Since it is the first occurance then it will list it in the first position available. I am at work at the moment but I will look at it again to see if an 'IF' formula might work. How many sets of data or how many occurances exist on your typical speadsheet? Bryan

0

between 2 to 114 occurences. Note that this formula =VLOOKUP($F1;$A1:$C10;3;FALSE) does excately the same as the macro.(copy-paste deosn't work, you have to redo the formula with the formula bar) Now I have two possibilities!

0

Lies, Lies, Damn Statistics. You can't average averages. An average of averages is meaningless if the sample sizes are not comparable. This is an extreme example why you cannot use percentages. Using your data format with 2 attibutes A & B T_1 A 1 10% B 9 90% Total 10 T_2 A 81 90% B 9 10% Total 90 By your method A B Tot T_1 10% 90% 100% T_2 90% 10% 100% Tot 100% 100% 200% Avg 50% 50% 100% By the correct method A B Tot T_1 1 9 10 T_2 81 9 90 Tot 82 18 100 82% 18% 100% Avg 41 9 50 82% 18% 100% As you can see that without the occurrences of each sample item, you have no true idea of the total number in each group and the total number observation in the whole data set. I hope this explains why you can't use the percentage value. I hope the format holds that you can see the columns.

0

The VLOOKUP formula you posted contains an error. Otherwise it is a normal VLOOKUP formula. It differs from the FindNth formula where it will only look up a single occurance. FindNth will look up several. However the FindNth is not applicable since each range does hold some relevance to your final table. I don't know how your data is compiled: be it new data is added or if each time you collect the data it creates a whole new sheet. Right click and select "Save Target As" on the following link. It contains no Macros. FredTest.xls 28.5kb. I noticed the server is a little slow connecting today. Although it will take much effort to do so I believe you need to name all of your ranges. If you have any questions then post back. Also let me know after you get the file so I can take it off of my personal webpage. HTH Bryan

0

I will remove the spreadsheet from my webpage on 14Sep PM unless advised otherwise. Bryan

0