• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculations

T

TonyNZ

Member
Hi there,

I have rather complex problem (well complex for me though).

I have say 3 students Chris, Sam and Matt. Each attending different set of classes each day over one week period. e.g. on Monday Maths/English, Maths/Accounts, Physics/Accounts, Lab/Chemistry Tuesday Lab/Chem, Accounts/Maths, etc. and so on. Based on the data, I need to do 2 sets of calculations. (1) Number of Maths classes (with any combination), and so on (2) Number of Maths only, Accounts only etc. attended by each student. I have a spreadsheet but not able to upload. Any help would be greatly appreciated. I have never done VBA stuff, so any simple non VBA solution, if possible, as a preferred option, would be greatly appreciated. (I want to upload spreadsheet but dont know how to)
 
TonyNZ


Firstly, Welcome to the Chandoo.org forums


Have a look at: http://chandoo.org/forums/topic/posting-a-sample-workbook


You may also want to note the 5 sticky green posts at: http://chandoo.org/forums/
 
Dear Hui

here is the link for sample file:


https://www.dropbox.com/s/12q8vjeze04399m/Calculations.xlsx


I was reading your other solutions in other forums; I know you will have solution to this.

Many thanks again

you guys rock

regards
 
TonyNZ


D12: =SUM(LEN($B$2:$F$7)-LEN(SUBSTITUTE($B$2:$F$7,C12,"")))/LEN(C12) Ctrl+Shift+Enter

Copy down


E24: =SUM(LEN(OFFSET($B$1:$F$1,MATCH($C$24,$A$2:$A$7,0),0))-LEN(SUBSTITUTE(OFFSET($B$1:$F$1,MATCH($C$24,$A$2:$A$7,0),0),D24,"")))/LEN(D24) Ctrl+Shift+Enter

Copy down for 1st student

You'll have to adjust manually for each subsequent student


To understand how these work refer: http://chandoo.org/wp/2011/11/07/formula-forensics-002/
 
Dear Hui

I will try working on this and let you know tomorrow.

I will try to create a macro for this to automate.

maaaaaaaaany thanks

Regards
 
You may also want to consider putting the Subjects in a Row and Names in a Column in the bottom example

This will simplify the formulas and give you a nice report

Refer: https://www.dropbox.com/s/yitkl2tzzw9uld7/Calculations.xlsx

eg:

[pre]
Code: 
Total	Maths	Chem	Accounts	Music	Phy Edu	Language	Lab	Eng	Accounts	Total
Chris	2	1	1	1	1	0	1	2	1	10
Sam	2	0	1	1	0	1	1	1	1	8
Jack	2	2	1	0	0	0	1	2	1	9
Bear	0	1	1	1	1	0	1	1	1	7
Ronald	2	1	1	1	1	0	1	2	1	10
Mattey	2	1	1	0	0	0	1	2	1	8
Total	10	6	6	4	3	1	6	10	6
[/pre]
 
Hi Tony ,


For the simple counts of the subjects , in the range D12 through D20 , the following formula can also be used :


=COUNTIF($B$2:$F$7,"*"&C12&"*")


Put this in D12 , and copy down.


Narayan
 
Dear Hui

Downloaded the spreadsheet,it makes sense.

Another question: I am sure this will work, albeit with some modification, if for some reason, there are more than 2 subjects mentioned in one cell e.g. Eng/Accts/Pshycho and so on.

Now the big question?

Would you be able to point me towards key formulas used and for me to learn these to understand better; the basic underline principle to be kept in mind while doing such things; and links within chandoo where I can find them easily; this would be big help.

I will play around more by adding more data and will keep you posted. Thanks again,

Kindest regards

T


Dear Naraynak - I will try your solution also and will keep you posted.

Kindest regards

T
 
Dear Hui

One more question:

In the actual worksheet, the data is in tables form imported via MS Query, and there are about 6 tables imported from 6 different sources; all compiled into one. I am sure, this solution, with appropriate modifications, will work collectively and give me results of all 6 tables rather than me to have set up such solutions for each table individually.

Regards

T
 
Dear Hui

Bit silly question: What do I do in the event I need to add another subject e.g. Business Studies as a stand alone and / or in combination with other subject?

Regards

T
 
Tony


My second post has a line:

To understand how these work refer: http://chandoo.org/wp/2011/11/07/formula-forensics-002/

I can't be much more specific than that


In regards adding other subjects, add away

extend the ranges to include all data (names and Subjects) as appropriate
 
You must log in or register to reply here.
Back
Top