Proprogate column calculations from row1 to rownn

[Guest]

I am trying to create a Master Grade table where a test may have a diffierent
number of questions, so wanted to set up row 1 than use it to create
additional rows up to number of questions on the test.

Compute percentage correct for 50 question text;
50 questions 50 answered right 100%
50 questions 49 answered right nn%
....
50 questions 1 answered right nn%

Can someone please suggest a method to replicate a cell computation to
additional rows when the number of rows required will be different use of
this workbook.

Thank yoiu.

 

[Bernie Deitrick]

A formula along the lines of

=COUNTIF(B:B,"Y")/COUNTA(B:B)

or

=COUNTIF(B:B,"Y")/(COUNTA(B:B)-1)

Where you enter Y in column B if the answer is correct and X if it is not...

HTH,
Bernie
MS Excel MVP

 

[Guest]

My wife is a teacher and would like to compute the percentage of questions
answered correctly based on the number of questions on a test. Example:

If there are 50 questions on a test, I would like to generate 50 rows where
each row follows pattern (50-1), (49-1), (48-1) etc. In the next colum, I
would like to compute the precent of correct answers 50/50=100%, 49/50=98%,
48/50=96% repeating this pattern until (1-1) = 0, as that would provide the
entire range of possible correct answers and there corresponding percentages
(1 right to 50 right).

What I started with was

Row1-nn Column B Column C
50 50 =sum(B1/A1)
=sum(b1-1) =sum(b2/INDIRECT("A2"))
=sum(b2-1) =sum(b3/INDIRECT("A2"))

I would like to repeat the pattern nn times where Excel replicates the
b2/b3, b3/b4 pattern until bnn-1 = 0.

Any clues?

Thank you.

newtomitch

 

[Bernie Deitrick]

newtomitch,

Enter the number of questions in cell A1 - in your example, 50.

In cell B1, use the formula
=A1

In Cell C1, use the formula
=B1/$A$1

In cell B2, use the formula
=IF(ROW()<=$A$1+1,B1-1,"")

In cell C2, use the formula
=IF(B2<>"",B2/$A$1,"")

Copy B2:C2 down at least as far as the highest number of questions you could have on a test - say,
to B101:C101.

Then format column C for percent with 0 decimals.

HTH,
Bernie
MS Excel MVP