Assiging a number value

**lmparedes** · 09-01-2009, 03:27 AM

How do I assign a number value to a letter.
I'm making a class attendence spread sheet.
I want to assign 10 points for "P" Present
7 points for "T" Tardy etc.
How do I prevent it from interfering with all the assigned P/Ts etc.

Thanks

**Palmetto** · 09-01-2009, 08:44 AM

There are a number of ways to go about this.

Here is one way.

Say cell B2 contains the letter "P" or "T".

Cell C2 could contain this formula:

=if(B2="P",10,if(B2="T",7,0))

**lmparedes** · 09-01-2009, 10:50 AM

Thank you. I entered the formula in Cell B2 but when I type the Letter "P" in the cell it overtypes the formula.

**sweep** · 09-01-2009, 10:57 AM

You could use the COUNTIF function to total points as discussed.

If you have the attendance record for a pupil in cells A1:A10,

This formula

=(COUNTIF(A1:A10,"P")*10)+(COUNTIF(A1:A10,"T")*7)

Will return the total points for that pupil.

**Palmetto** · 09-01-2009, 11:49 AM

I entered the formula in Cell B2 but when I type the Letter "P" in the cell it overtypes the formula.

As I mentioned in the first post, you would place the formula in cell C2 and make entries of "P" or "T" in cell B2.

A cell can contain ONLY a value directly entered or a value that is the result of a formula. As you have discovered, formulas are overwritten when data in directly entered into the cell (unless you apply protection to the cell/worksheet).

Bottom line: use cell B2 for data entry and cell C2 as a formula cell to determine which value to assign to the letter in B2.

See these links for Excel Training Resources.

Free EXCEL Training
Contextures

**lmparedes** · 09-01-2009, 09:05 PM

Originally Posted by sweep

You could use the COUNTIF function to total points as discussed.

If you have the attendance record for a pupil in cells A1:A10,

This formula

=(COUNTIF(A1:A10,"P")*10)+(COUNTIF(A1:A10,"T")*7)

Will return the total points for that pupil.

Thanks! That worked great. How do I assign a cell, that will have no data enetered into it, a number value.

**lmparedes** · 09-01-2009, 09:07 PM

Originally Posted by Palmetto

As I mentioned in the first post, you would place the formula in cell C2 and make entries of "P" or "T" in cell B2.

A cell can contain ONLY a value directly entered or a value that is the result of a formula. As you have discovered, formulas are overwritten when data in directly entered into the cell (unless you apply protection to the cell/worksheet).

Bottom line: use cell B2 for data entry and cell C2 as a formula cell to determine which value to assign to the letter in B2.

See these links for Excel Training Resources.

Free EXCEL Training
Contextures

I see my error. I can not enter data in a cell where a formula is being used.
Thanks! How do I assign a cell, that will have no data enetered into it, a number value.

**sweep** · 09-02-2009, 06:07 AM

To extend my original example:

If cells containing P are worth 10, those containing T are worth 7, and those that are blank are worth 3;

=(COUNTIF(A1:A10,"P")*10)+(COUNTIF(A1:A10,"T")*7)+(COUNTIF(A1:A10,"")*3)

**Palmetto** · 09-02-2009, 08:10 AM

How do I assign a cell, that will have no data enetered into it, a number value.

My original formula assigns a value of zero if the cell doesn't contain "P" or "T". If the cell is empty what value do you want to assign to it?

You could just change the last argument from zero to another number.
=IF(B2="P",10,IF(B2="T",7,3))