teacher's questions

[Guest]

Hi,

I'm a teacher trying to make my life easier but…

Any help would be appreciated, thanks.


I have a few questions concerning a spreadsheet.

It is used to calculate pupils Levels (range 1-9) from their test
scores.

The idea is that you enter their test scores in one column and the
spreadsheet enters the correct level in the cell next to it.

It all works but I have a few simple questions.


Q1. If the score in a cell is zero the corresponding level cell
displays #N/A

Is it possible to have this display something more sensible, say 0 or
just blank?



Q2. in cell I12 I have the formula =COUNTIF(E5:E39,"=2")

The "=2" refers to the level in cell G12

I tried to reference the cell g12 directly by making the formula
read

=COUNTIF(E5:E39,=G12)

but this doesn't work. I tried putting quotes around it but
that didn't help.

G12 just contains a number (in the range 2 to 9)

It seams sill to have to enter the number again, if the number
ever changes I have to remember to change it in two places, doesn't seem
efficient to me.



Q3 Is it possible to make some cells un-editable to stop users
accidentally corrupting cells.

They only need access to some of the cells to enter pupils name,
score and the level threshold.

Thanks

 

[Frank Kabel]

Hi

Q1. If the score in a cell is zero the corresponding level cell
displays #N/A
Is it possible to have this display something more sensible, say 0 or
just blank?

=IF(A1=0,"",Calculate level)
where A1 holds your test score

Q2. in cell I12 I have the formula =COUNTIF(E5:E39,"=2") [snip]
=COUNTIF(E5:E39,=G12)

change to
=COUNTIF(E539,G12)

Q3 Is it possible to make some cells un-editable to stop users
accidentally corrupting cells.

1. Goto 'Format - Cells - Protection' and uncheck the protection only
for the cells for which you want to allow editing.
2. Goto 'Tools - Protection' and look your worksheet or workbook

HTH
Frank

 

[Bob Phillips]

Q1. Whatever the test is that you have needs to be checked for #N/A and
return a blank if so. For example

=IF(ISNA(original_test),"",original_test)

Q2.

=COUNTIF(E5:E39,"="&G12)

Q3.

You can, but you need to unlock all of the editable cells
(Format>Cells>Protection), and then protect the sheet
(Tools>Protection>Sheet).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Terry Bennett]

Some ideas:

Q1: you could use ISERROR(). So if you have a formula in cell J24:
=H24/H25, whenever H25 is 0 an error will be returned. Change the formula
to =IF(ISERROR(H24/H25),"",H24/H25) and the cell will be blank whenever an
error is returned.

Q2, try: =SUMPRODUCT((E5:E39=G12)*1)

Q3: To 'lock' cells use Format/Cells/Protection alongside Tools/Protection.
So, manually select all of the cells on the sheet that you want users to be
able to amend/edit. With these selected, go to Format/Cells/Protection and
remove the 'tick' from 'Locked'. These cells are then unlocked. However,
none of this takes effect until you protect the sheet using
Tools/Protection/Protect Sheet.

Hope these help!

Terry

 

[Guest]

Thanks.

Hi

Q1. If the score in a cell is zero the corresponding level cell
displays #N/A
Is it possible to have this display something more sensible, say 0 or
just blank?

=IF(A1=0,"",Calculate level)
where A1 holds your test score

Q2. in cell I12 I have the formula =COUNTIF(E5:E39,"=2") [snip]
=COUNTIF(E5:E39,=G12)

change to
=COUNTIF(E539,G12)

Q3 Is it possible to make some cells un-editable to stop users
accidentally corrupting cells.

1. Goto 'Format - Cells - Protection' and uncheck the protection only
for the cells for which you want to allow editing.
2. Goto 'Tools - Protection' and look your worksheet or workbook

HTH
Frank

 

[Guest]

Thanks

 

[Guest]

Thanks

Some ideas:

Q1: you could use ISERROR(). So if you have a formula in cell J24:
=H24/H25, whenever H25 is 0 an error will be returned. Change the formula
to =IF(ISERROR(H24/H25),"",H24/H25) and the cell will be blank whenever an
error is returned.

Q2, try: =SUMPRODUCT((E5:E39=G12)*1)

Q3: To 'lock' cells use Format/Cells/Protection alongside Tools/Protection.
So, manually select all of the cells on the sheet that you want users to be
able to amend/edit. With these selected, go to Format/Cells/Protection and
remove the 'tick' from 'Locked'. These cells are then unlocked. However,
none of this takes effect until you protect the sheet using
Tools/Protection/Protect Sheet.

Hope these help!

Terry

 

[Guest]

Thanks everyone.

I have made the changes and it has made my life a lot easier.

Have a virtual beer on me.

Thanks again