Add contributing %s to max 100% total

[halesowenmum]

Hi.

I have a spreadsheet

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Question 1[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Q1 assessment criteria 1[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]

Q1 assessment criteria 2​

[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]

Q1 assessment criteria 2​

[/TD]
[TD]40%[/TD]
[/TR]
</tbody>[/TABLE]









Under Question 1 there are three things users have to report on their progress with. They type in the % figure and CF produces a horizontal progress bar. As we can see it's all hunky dory here as 40 30 and 20 does indeed equate to 90%.

I think it's when the numbers go over what would add up to 100 that we get into trouble.

What about when all three have gotten 80% of the way through - the value in E1 would be 240%, if they all mark that they've finished by typing in '100' the total will show as 300% - which is not what we need.

How do I write the formula in E1 that allows people to put in their progress as described - which is a requirement, but then the total in the E1 would be a cumulative total of their contribution towards the overall goal of reaching 100% ie all sub-tasks completed = 100%.

Can anybody help me with this (quite garbled) explanation I've provided?!

 

[FormR]

Hi, isn't it just:

=SUM(E2:E4)/3

 

[halesowenmum]

Hi, isn't it just:

=SUM(E2:E4)/3

Maths is NOT my forte I'm sorry to say.

I amended the formula and yes, that's all it needed.

Thank you.

 

[halesowenmum]

Is there a way of automating the /3 number part?

what I mean is, there could be a differing number of elements - it might be three contributing scores, it could be 23 - is there a way for the formula to say 'sum the value immediately below this cell and everything up to the row where Col D contains a bold heading then divide the sum of those values divided by the number of values' - because otherwise you'd be having to count and manually adjust each formula on each heading row.​

 

[FormR]

Is there a way of automating the /3 number part?​

​

Hi, you could try something like:

=SUM(E2:E4)/COUNTA(D2:D4)