Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proprogate column calculations from row1 to rownn
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proprogate column calculations from row1 to rownn
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 "newtomitch" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proprogate column calculations from row1 to rownn
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" wrote: 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 "newtomitch" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proprogate column calculations from row1 to rownn
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 "newtomitch" wrote in message ... 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" wrote: 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 "newtomitch" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the row1 content of the existing excel file automically | Excel Worksheet Functions | |||
How to get row2 value into row1 in calculated Field Column in pivo | Excel Discussion (Misc queries) | |||
VB Code is working for row1; how to apply to row2? | Excel Programming | |||
VBA to Identify changes in row1 | Excel Programming | |||
VBA dynamically format Row1 | Excel Programming |