assigning value to a letter (Excel 97)

M

mac

Hi,

At my place of work we have a roster. To indicate what day
you are working a letter is put in a cell for that
particular day e.g

O = Operational Day (11 hours)
X = Normal Day (8.5 hours)
A = Admin day (8.5 hours)..

an so on...

Your weekly hours are totaled up at the end of the week
(Sat) depending on what you worked (as above).

What I would like to do is assign a value to whatever
letter is in the cell and in another cell somewhere in the
worksheet total up the hours for that week. Sadly I have
no idea how to do this.

This is useful so you can work out your projected hours to
see whether you are going to be down or up in hours in
advance

But I have no idea how to go about this. I have started
learning vb so my knowledge is not that good yet.

I hope this all makes sense.

Can anybody point me in the right direction please?

Many thanks

mac
 
R

Roger Govier

Hi Mac

One way would be to use the Sumproduct function and an arry to represnt your
letters and hour values
=SUMPRODUCT((A1:A100={"O","X","A"})*({11,8.5,8.5}))

Change the range A1:A100 to suit your own data range for each column
 
M

Mark

You can create a copy of the rosta either somewhere on the
same sheet or in a new sheet and use formulas to work out
the hours.

If Cell A1 contained either O,X or A you could calculate
the hours like this:

=IF(A1="O",11,IF(A1="X",8.5,IF(A1="A",8.5,0)))

You would have to repeat this formula for every cell that
contained a code of O,X or A

Another solution would be to add an extra column onto each
workday and insert the actual hours worked. Then simply
add up the value column for each day to get the weekly
total.
 
M

mac123

Roger,

Thanks for the reply.

I've tried the function you suggested but all I'm getting
is: #N/A

All I've changed is the cell range from A1:A100 to B10:H10

Some the cells in that range have no letters in them
(meaning i'm not working that day). Could this be the
problem? or am I doing something else wrong?
 
M

mac123

Mark,

Fantastic it works but like you said I have to apply this
to all the cells. Not a problem though as once its done
thats it!

Thanks a million for that.

mac
 
M

mac123

Mark,

One last thing..is there a limit to how many 'if'
statements you can have. We have a total of 15 codes that
can be used in our roster. I tried expanding the function
to also evaluate AL code (Annual Leave) but it complained
and said too many arguments. is this because the code AL
consists of 2 letters or is this an 'if' too many?

mac
 
M

Mark

I think the limit is seven IF statements, and you are also
limited by the number of characters making up the formula
so this isn't the ideal solution.

I would go for the additional column method. This would
allow you to use a VLOOKUP to a table of codes and the
hours associated with them.
 
Top