Dates into percentages

[CelticCharmer]

Hi,
I was wondering if you could help me out. I train students and when a
student completes an element of their course work I input a completed date
into a cell. What I want to do is as soon as I input the completed date I
want excel to work out the percentage as they complete the course e.g. 6%,
15%, 75%, etc until they are finished 100%.
The amount of elements the students do changes from student to student from
28 to 34. What formula I can use? Do I have to change the formula from
student to student to get 100%?
If you need any more information please ask.

 

[Rick Rothstein]

I think you will need to describe your layout to us (what data is in what columns and/or rows).

 

[Shane Devenshire]

Specifically how do you determine what percentage each element is worth of
the whole? If there are two elements do they automatically get 50% each or
can one be worth 25% the other 75%?

 

[CelticCharmer]

Hi and thank you both for your replies.

From cell H8 to BW7 (68 elements) I input dates as they complete each
element (21/01/09)
Students do not do all 68 elements this ranges from 28 to 34 elements this
will be the 100%.
I want to put in the progression percentage in cell BX8 so I show how much
is completed out of 100%

The cells I use range from H7 to BW7 as you can see I don’t use them all and
it depends which elements the student use.
Student 1 = I input dates (over a six month span) into cells H7 to K7, L7 to
O7, V7 to Y7, Z7 to AC7, AH7 to AK7, AP7 to AS7 and BN7 to BQ7. That’s 28
cells which will equal 100%.
I want to see what percentage has been completed in cell BX7.
I hope this makes it easier for you
Celtic charmer

 

[Rick Rothstein]

If the number of elements a student completes can vary between 28 and 34, do you know **in advance** which number of elements they will ultimately complete? If not, then there is no way to give a meaningful percentage progress report. What would you divide by to get that percent... 28, 29, 30, 31, 32, 33 or 34? The problem, for example, when a student has completed 27 elements, they are 96.4% complete if they will be doing 28 elements, but only 79.4% complete if they will be doing 34 elements. On the other hand, if you do know what the ultimate number of elements they will complete are, then what column is that value located at in?

 

[CelticCharmer]

Hi,
The student tells me at the start what elements they will do. The example I
gave is the most common elements they pick and it's the cell numbers I also
use.Is there a way you can see what I am working on? If thats any help.
Thank you for your time
Celtic Charmer.

 

[Rick Rothstein]

The student tells me at the start what elements they will do.

And where is that information encoded at on the worksheet? I need either the count of those elements or an indicator of some kind for the elements each student plans to do so I can count them. In order to get a percent complete, you have to divide by that count and you haven't told us, yet, how to read that count from your worksheet.

 

[CelticCharmer]

Is there anyway you can see it? or can I send?

Celtic Charmer

 

[Rick Rothstein]

I don't think I need to see it... all you have to do is tell me how you know how many elements the student told you he/she would be doing and where that is encoded on your worksheet. Showing me the worksheet won't help if this information is not on it; and if it is on the worksheet, just tell me where.

 

[CelticCharmer]

Hi Rick and thank you very much for your time.
I input dates into cells H7 I7 J7 K7, L7 M7 N7 O7, V7 W7 X7 Y7, Z7 AA7 AB7
AC7, AH7 AI7 AJ7 AK7, AP7 AQ7 AR7 AS7 and BN7 BO7 BP7 BQ7. I do this as each
element is completed by the student. That’s 28 cells which will equal 100%.
is this the encoded you require?
Celtic charmer, again thank you for your time.

 

[Rick Rothstein]

No, that does not tell me what I need to know. Let's say the student has
completed 7 elements, so you have put the completion dates in these cells...

H7 I7 J7 K7, L7 M7 N7

My question to you is, at the point in time when you enter a date into N7,
how do you know the student is on his/her way to completing a total of 28
elements? How do you know, AT THAT POINT IN TIME, they are not going to end
up doing 34 elements instead of 28? You said the student tell you how many
elements they will be doing. Let's assume the student told you they will be
doing 28 elements... where on your worksheet do you have that 28 entered for
that student? The reason I keep asking you this question is because whatever
number of elements the student told you they will be doing... I have to
divide by that number.

--
Rick (MVP - Excel)

Hi Rick and thank you very much for your time.
I input dates into cells H7 I7 J7 K7, L7 M7 N7 O7, V7 W7 X7 Y7, Z7 AA7 AB7
AC7, AH7 AI7 AJ7 AK7, AP7 AQ7 AR7 AS7 and BN7 BO7 BP7 BQ7. I do this as
each
element is completed by the student. That’s 28 cells which will equal
100%.
is this the encoded you require?
Celtic charmer, again thank you for your time.

 

[CelticCharmer]

I am sorry if I am not explaining this well.
I have 68 elements in total from cell h7 to bw7.
I know from day one what elements the student will be doing because they
tell me.
At the moment I just put the dates into the elements as they complete each
one.
Some elements have to be done with others but students mainly pick the list
of elements I listed 28 in total. At other times students may pick different
elements that have to be done with others which will bring their total up to
34 but let’s not worry about that for now.
I don’t know if this helps ??? again I am sorry

 

[Rick Rothstein]

I know from day one what elements the student will be doing

because they tell me.

And my question is... after they tell you how many elements they will be
doing at day one, do you put that information into your worksheet anywhere?
If so, tell me where. If not, you will have to; otherwise, you will never be
able to divide by the correct number in order to get a meaningful percentage
complete. You can't just use 28 as the divisor for all situations... if the
actually are going to do 34 elements, then when they complete 29 of them,
using 28 as the divisor would generate a completion percentage of more than
100%.

 

[CelticCharmer]

Hi Rick
I have all possible elements on my excel sheet from cell columns h6 to bw6.
Then the students names go into the cell rows numbers 7, 8, etc... So the
first date input for student 1 will be made in cell h7 and the last possible
input will be in cell bw7
I have to have all 68 possible elements because some students may want to
different ones.
Maybe I'm trying the impossible?
Thanks for you time,
Celtic Charmer

 

[Rick Rothstein]

Do you put those dates into the cells when the student tells you what
elements he/she is going to do (sort of like target dates) and then do you
put the actual completion dates in a different row as the student completes
the element?

 

[CelticCharmer]

Hi Rick,
I put the dates in as the student completes each element in the same row as
the student is on.

 

[Rick Rothstein]

Then I need to ask again... how do you know in advance (that means, BEFORE
they complete ALL of the elements they intend to do) BY LOOKING AT YOUR
WORKSHEET how many elements the student told you they were going to do? I
repeat... in order to calculate the percentage that you want, you MUST know
the number of elements the student will ultimately do so you can divide by
it. If that number is not on your worksheet, or cannot be counted in some
way BEFORE the student completes ALL of his/her elements, then you cannot
get the percentage complete figure you asked for.

Perhaps if I state my question in a different way, you will see what I am
looking for. Pretend its the beginning of the school year. You ask your
students how many elements they plan to do. The next day (second day of the
school year), I come up to you while you are looking at the worksheet and
ask... how many elements did "Student A" tell you they were going to do?
What would you tell me? Assuming you give me a number... where did you get
that number from? That number is what I need to know in order to calculate
the percentage complete.

--
Rick (MVP - Excel)

Hi Rick,
I put the dates in as the student completes each element in the same row
as
the student is on.

 

[CelticCharmer]

Hi Rick.
well as i Have them in my work sheet already all 68 elements of them. I know
the elements the student is doing via his/her learning plan in their folder,
which we do at the start of the course. I then fill them in as the student
completes each element.
I put a X into the cells(elements) they are NOT doing and that leaves blanks
where I will input the dates as they are completed.

 

[Rick Rothstein]

Having the elements the student plans to do in a location other than the
worksheet (namely, the student's folder) does not help (I can't write a
formula to look into that folder<g>). Can you add a column to your worksheet
and put the total number of elements the student plans to do in it? If so, I
should be able to give you the formula for the percentage complete that you
want; if you can't add this column, the you will not be able to do what you
want.

 

[CelticCharmer]

Hi Rick
yes I can add more columns at the end which will be bx8 onwards as many as
you think I need. Student a is row 8 and not 7 as I have stated sorry.