Solved

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

Posted on 2002-03-09
6
1,373 Views
Last Modified: 2007-12-19
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
Comment
Question by:sandra_8309
6 Comments
 
LVL 4

Expert Comment

by:mousmasterbob
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

by:
ture earned 200 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
Katlstad, Sweden
0
 
LVL 15

Expert Comment

by:dbase118
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

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

Expert Comment

by:WJReid
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

by:ture
ID: 6861375
sandra_8309,

It's been a while... Did any of our suggestions help you?

/Ture
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question