Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Variable Ranges and Combining Data & Text

    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

  2. #2
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Combining Text and Data Problem Resolved

    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

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi John,

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

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Resolving Range Problem

    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)

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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
    Code:
    =MATCH("*",R:R,-1)
    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().
    Code:
    =R5:INDEX(R:R,MATCH("*",R:R,-1))
    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 >
    Code:
    Name: STUDENTS
    Refers to: =$R$5:INDEX($R:$R,MATCH("*",$R:$R,-1))

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •