Greetings all,

I am an Access person, and a bit of an Excel virgin. Be gentle! I am trying to fit a Chinese form with Excel so that foreign teachers (mostly used to using computers for getting dates) do not have to do numerous, redundant calculations by hand (making incredible calculation errors).

At my school in China ( 广西大学）， we have to provide paperwork on exam results. For example, how many people get between 90-100%, etc). I have set up the calculations no sweat WHEN I know the range of the students (for example, how many students there are). I need to know how to set the range for a variable number of students (for example, 35 as opposed to 20 students). Should I test which last cell is a number as opposed to text or something else? I also need to know how to combine a calculated number with text. For example, if the average for all students is 85%, how do I specify in a single cell: " average: 85% for all students".

Remember, I am a virgin: be gentle.

Regards

John S
Guangxi University
Nanning, China

John Again,

I have resolved the problem of combining text with a calculated value. I also resolved the problem of formatting the data using a TEXT function. Now on to the range problem. Sometimes, if I have 30 students, my range will be $R$5 to $R$36. However, this changes if I have 40 students. How I dynamically determine the range?

Regards

John S
Guangxi U

Hi John,

Do you want to determine the range dynamically by using formulae or by using VBA?

Hi Colin,

Thanks for the reply. I would prefer to use a formula; I have not yet used VBA with Excel (though I use it extensively in MS Access). If I was really clever, I would use range names, and set the range name dynamically (assuming this is possible).

I did think about trying to do this by testing for numeric or non-numeric. I am sure others have run up against this problem.

Regards

John S
Guangxi U.
Nanning China (currently)

Hi John,

Dynamic ranges are the way to go. These automatically update to reference the data as it changes.

There are lots of different formulas you can use, each of which is suitable in different situations. Here it sounds like you are working with text data types (names of people) within a single column (column R).

The formula
would find the last row in column R containing some sort of text. You can convert this into a range reference R5:Rx several ways, one of which is using INDEX().
If the last row is 6 then it returns a reference to R5:R6. etc..


To avoid the inconvenience of having to use this long formula within other formulas, we could use a name to refer to it, thus making it a named dynamic range.

Define name >

To use it from other formulas we can now just use the name STUDENTS. For example, how many students have you filled in? Answer:
I'm not sure exactly what you're going to use this for but I hope it helps...