Giving a letter a numerical value

[Guest]

Hi, I'm creating a roster, using the values "A" "B" and "C" for the different
shifts and I would like to be able to give each value the numerical value of
however many hours each shift is, without making the text in the cell change
to a number, but still be able to add up the number of hours at the end of
the line. In other words A = 8, B = 10, C = 12 and the total is 30. Hope
this makes sense. I am using MS Excel 2002. Many thanks! Mullet2262

 

[Guest]

Use like SUM ie. =SumABC(A11)

Function SumABC(ByVal rng As Range)
Dim ABCsum As Double
ABCsum = 0
For Each cell In rng
Select Case cell
Case Is = "A"
ABCsum = ABCsum + 8
Case Is = "B"
ABCsum = ABCsum + 10
Case Is = "C"
ABCsum = ABCsum + 12
End Select
Next cell
SumABC = ABCsum
End Function

 

[Guest]

Hi,
for each cell you will input shift you define cell format/define/"A";"" or
"B";"" or "C";"", then you can total the numbers you have input the cells.
(the cell format for total is defined as normal numerical)

wdjsxj

“Mullet2262â€ç¼–写：

 

[Guest]

Hi,

With your data in A1:A3, enter this formula into your total cell:

=SUM(COUNTIF(A1:A3,"A")*8+COUNTIF(A1:A3,"B")*10+COUNTIF(A1:A3,"C")*12)

Ewan.

 

[Flintstone]

Hello

Try this

=CODE(UPPER(A1))-64

Matt

 

[Guest]

Hi all,

Thank you all for your fast replies - I really appreciate them. What I
really should have added in my original message was that I know how to use
Excel on a basic level but not advanced formula level - sorry!!!!

Perhaps it would help if I expanded on my requirements:

Across the top of the spreadsheet will be the dates for that month - so 1/1,
2/1, 3/1 and so on to the 28/1 in each cell (A!: AB1)

Down the side of the ss will be the names of the 5 staff working the
shifts.(A2:A7)

There are three shifts available (ultimately there will be more than this -
up to 20 staff picking up varius shifts through the month but that comes
later - for now just focussing on the 5 senior staff) - A(7-4), B(2-11)
C(11-8) These are 9 hour shifts but later the hours will vary acording to
shift requirements.

At the end of each row(AC1), I would like to be able to add up each row so
that the total is the number of hours for that row - that way, I can see if
that staff is over or under the number of hours required in their contract
for the month. It saves having to add up the hours every time a change is
made - and rosters are a hair pulling exercise, bad enough when you have 5
staff but very frustrating when you have 20 or more staff to do on the one
roster - you get sick of adding the shifts up to make sure each line works
out. It is no good manually inputting the hours (8,9 etc) in each cell in
each row because that does not tell me which of the shifts they have and thus
covering the 24 hours of that day.

So, I'm after a way to be able to enter the text A, B or C or even A1, B1
etc and it will remember that as the numeric value for that text and add the
values up at the end of each row.

Hope all this makes sense again.

Again, thank you all for taking the trouble to help - I really appreciate it!!

Mullet2262

 

[daddylonglegs]

If the shift lengths are going to change then where will you store that
information? You'll only be able to do what you want if you can
reference the correct shift lengths.

Going by your original example where A = 8, B10 and C=12 then to
calculate total hours for the row B2:AB2 you could use this formula in
AC2

=SUMPRODUCT(COUNTIF(B2:AB2,{"A","B","C"}),{8,10,12})