Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# working attendance table on a spreadsheet (ms excel 97/2000)

Posted on 2002-03-09
Medium Priority
1,394 Views
I’m trying to create a working attendance table on a spreadsheet (ms excel 97/2000).
----------------------------------------------------
F: 1 day
H: 0.5 day
A: 0 day
S: 0 day

F: full day, H: half day, A: Absence, S: Sick

One table represent a worker attendance that have 31 cells which means 31 days. So if I enter F in a cell and it will give 1 day value, H will give 0.5 day, A and S will give 0 day value. Then, there will be a cell to show total of working days for a worker.
---------------------------------------------------------
Can someone show how to solve the above problem?

Thanks.
0
Question by:sandra_8309

LVL 4

Expert Comment

ID: 6852663
sandra

I set up table in I1,I2,I3,I4 were I1=1,I2=.05 and so on
days are in column 1 letters are typed in column 2 formula is in column 3 copy this down

=IF(B2="f",\$I\$1,IF(B2="h",\$I\$2,IF(B2="a",\$I\$3,IF(B2="s",\$I\$4))))

...Bob
0

LVL 22

Accepted Solution

ture earned 800 total points
ID: 6852785
sandra_8309,

With your 31 cells in A1:A31, this formula should do what you want:
=COUNTIF(A1:A31,"F")+COUNTIF(A1:A31,"H")*0.5

Ture Magnusson
0

LVL 15

Expert Comment

ID: 6854139
I used a setup similar to mousemaster but used a VLookup formula in column three like

=VLOOKUP (B2,I1:J4,2) then copy it all the way down
0

LVL 15

Expert Comment

ID: 6854709
Whoops...middle part of formula would be absolute reference like \$I\$1:\$J\$4
0

LVL 13

Expert Comment

ID: 6856170
With your 31 days in cells a1:a31 and your f,h,a and s to be filled in cells B1:b31. The formula in cell c1 should be =(b1="h")*0.5+(b1="f"). Copy this formula to cells C2:C31.
The formula in Cell C32 should be as suggested from Ture
0

LVL 22

Expert Comment

ID: 6861375
sandra_8309,

/Ture
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.