Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing, counting, if, sum... help...
I have a spreadsheet of student data and I want to analyse data in four
columns in particular. The columns identify school terms and contain numeric values which identify a school subject code. (Codes run from 1-15). I need to count each subject a student studied over the four terms and I can do this easily when the codes refer to single subjects. (I had help from T.Valko and Teethless Mama - thank you both!) However, when I went back to double check the data I realised that there was a complication. Codes 1 to 4 identify four different subjects, while codes 5 to 15 represent various combinations of those four subjects. e.g. Code 5 means the student studied subject codes 1 and 4; code 7 equals subjects 3 and 4, and 6 equals subjects 1, 2 and 4, and so on for the rest of the codes. The data looks something like this in the spreadsheet: Term 1-----Term 2-----Term 3-----Term 4 1 ------------ 1 ---------- 4 ----------10 0 ------------ 1 -----------1 ---------- 1 10----------- 2 -----------1 -----------6 4 ------------ 0 -----------7 -----------1 Is there a way to count each subject only once? And can it be done in Excel 2003? Thank you. LisaM (This was my original query on funtions - http://www.microsoft.com/office/comm...=en-us&m=1&p=1) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing, counting, if, sum... help...
If you're counting each subject I'd assume you are using something like:
=COUNTIF(A1:D10,1) which will count all instances of subject code 1. Now if some other codes represent multiple subjects then you just include this in the countif. So, if code 5 represents subject codes 1 and 4, then when you're counting subject codes 1 and 4 you need to add the count of subject code 5. You also mention code 6 represents subject codes 1,2 and 4. So, to count all instances of subject code 1 use the following: =COUNTIF(A1:D10,1)+COUNTIF(A1:D10,5)+COUNTIF(A1:D1 0,6) which simply counts all instances of the subject codes that represent subject 1 and adds them together --- Add a countif for each code that represents the subject being counted. Jay __ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing, counting, if, sum... help...
Thanks Jay!
I should have made it more clear that I don't want to count each instance of, for example, code 1, whether it appeared as a 1 or as included in code 5, only unique values. However, I managed to use a formula to count cells where I'd already broken the codes down into the subjects they represented, so I have the result I was looking for after all. Thank you for your help, anyway. :) Formulae I can manage, but trying to get my head clear on functions, nesting functions, making sure all the arguments are included (and how they can be part of arguments for other nested functions) and that I haven't forgotten a bracket somewhere - phew! Head spinning!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing, counting, if, sum... help...
Just in case anybody's interested in how I finally got to count each subject
only once (rather than each instance of it) this is what I did. Each subject was represented by a code, but I had also given codes to instances where a student had studied two or more subjects in the same term. For Subject 1, for instance, the code was 1, but if a student had studied it along with Subject 2 the code was 5. And so on for other combinations. I set up Cols E, F, G, H (one for each subject) and named them "# Terms Sub 1/2/3/4 studied" and inserted the following function (the same one Jason suggested): =countif(A2:D2,1)+countif(A2:D2,5)+..... etc .... where Cols A through D were the four terms in the school year. All I needed to do was change the value being searched for in each of the "# Terms" columns. I then copied the function down through all the rows of data. Finally, in Col I, I inserted a function to count how many of the columns E through H had values greater than 0: =countif(E2,"0")+countif(F2,"0")+countif(G2,"0" )+countif(H2,"0") and copied the function down through all the rows of data. Job done! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing & Counting Data | Excel Worksheet Functions | |||
Counting days comparing 2 dates excluding empty cells | Excel Worksheet Functions | |||
Comparing and counting occurences in 2 columns | Excel Worksheet Functions | |||
Counting values and comparing them to a single cell | Excel Discussion (Misc queries) | |||
Comparing Columns and Counting | Excel Discussion (Misc queries) |