Formula for calculating ratio

[stusam]

Hi.
I work in education, and I'm currently working on a new way to plan our online courses. Part of this includes ensuring a balance of "Activity types". We have four possible Activity types to choose from:

• Directed Learning: Activity
• Directed Learning: Content
• Directed Learning: Live Session
• Guided Independent Learning

Using the sheet below, I'm trying to write a formula to calculate the ratio/balance of activity types (Column A) based on their duration (Column B). The snag being, I need durations for all three "Directed Learning" activity types to be grouped together, so that the ratio (column C) shows the balance between Directed Learning to Guided Independent Learning. Is anyone able to help, as I'm absolutely stumped?

! NEW Module Map Template V2.3.xlsx
	A	B	C
1	Activity Type	Acrvity Duration (Minutes)	Activity Ratio (Directed Learning : Guided Independent Learning)
2	Directed Learning: Live Session	90
3	Directed Learning: Activity	10
4	Directed Learning: Content	20
5	Directed Learning: Activity	10
6	Directed Learning: Activity	10
7	Directed Learning: Content	30
8	Guided Independent Learning	120
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	List	Directed Learning: Activity, Directed Learning: Content, Guided Independent Learning, Directed Learning: Live Session

Thank you.

 

[stusam]

Hi.
I work in education, and I'm currently working on a new way to plan our online courses. Part of this includes ensuring a balance of "Activity types". We have four possible Activity types to choose from:

• Directed Learning: Activity
• Directed Learning: Content
• Directed Learning: Live Session
• Guided Independent Learning

Using the sheet below, I'm trying to write a formula to calculate the ratio/balance of activity types (Column A) based on their duration (Column B). The snag being, I need durations for all three "Directed Learning" activity types to be grouped together, so that the ratio (column C) shows the balance between Directed Learning to Guided Independent Learning. Is anyone able to help, as I'm absolutely stumped?

! NEW Module Map Template V2.3.xlsx
	A	B	C
1	Activity Type	Acrvity Duration (Minutes)	Activity Ratio (Directed Learning : Guided Independent Learning)
2	Directed Learning: Live Session	90
3	Directed Learning: Activity	10
4	Directed Learning: Content	20
5	Directed Learning: Activity	10
6	Directed Learning: Activity	10
7	Directed Learning: Content	30
8	Guided Independent Learning	120
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	List	Directed Learning: Activity, Directed Learning: Content, Guided Independent Learning, Directed Learning: Live Session

Thank you.

Just to add, it doesn't need to display the balance as a ratio, it could be a presented as a percentage split, or any other way that allows me to see how the activities are weighted throughout the course.

 

[Peter_SSs]

Is this what you mean?

23 01 14.xlsm
	A	B	C
1	Activity Type	Acrvity Duration (Minutes)	Activity Ratio (Directed Learning : Guided Independent Learning)
2	Directed Learning: Live Session	90	58.62% : 41.38%
3	Directed Learning: Activity	10
4	Directed Learning: Content	20
5	Directed Learning: Activity	10
6	Directed Learning: Activity	10
7	Directed Learning: Content	30
8	Guided Independent Learning	120
Split

Cell Formulas
Range	Formula
C2	C2	=TEXT(SUMPRODUCT(--(LEFT(A2:A8,1)="D"),B2:B8)/SUM(B2:B8),"0.00%")&" : "&TEXT(1-SUMPRODUCT(--(LEFT(A2:A8,1)="D"),B2:B8)/SUM(B2:B8),"0.00%")

 

[stusam]

I think it just might

Is this what you mean?

23 01 14.xlsm
	A	B	C
1	Activity Type	Acrvity Duration (Minutes)	Activity Ratio (Directed Learning : Guided Independent Learning)
2	Directed Learning: Live Session	90	58.62% : 41.38%
3	Directed Learning: Activity	10
4	Directed Learning: Content	20
5	Directed Learning: Activity	10
6	Directed Learning: Activity	10
7	Directed Learning: Content	30
8	Guided Independent Learning	120
Split

Cell Formulas
Range	Formula
C2	C2	=TEXT(SUMPRODUCT(--(LEFT(A2:A8,1)="D"),B2:B8)/SUM(B2:B8),"0.00%")&" : "&TEXT(1-SUMPRODUCT(--(LEFT(A2:A8,1)="D"),B2:B8)/SUM(B2:B8),"0.00%")

That's perfect! Thank you so much.

Now it's time to decode the formula, so I can write it myself in future.

Very much appreciated.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Actually, you could instead consider this shorter, more efficient, one.

23 01 14.xlsm
	A	B	C
1	Activity Type	Acrvity Duration (Minutes)	Activity Ratio (Directed Learning : Guided Independent Learning)
2	Directed Learning: Live Session	90	58.62% : 41.38%
3	Directed Learning: Activity	10
4	Directed Learning: Content	20
5	Directed Learning: Activity	10
6	Directed Learning: Activity	10
7	Directed Learning: Content	30
8	Guided Independent Learning	120
Split (2)

Cell Formulas
Range	Formula
C2	C2	=TEXT(SUMIF(A2:A8,"D*",B2:B8)/SUM(B2:B8),"0.00%")&" : "&TEXT(1-SUMIF(A2:A8,"D*",B2:B8)/SUM(B2:B8),"0.00%")