numbers represented by letters.

  • Thread starter Thread starter chunky
  • Start date Start date
C

chunky

hello folks :)

i have a shift pattern.

a shift =8 hrs.
b shift=11 hrs.
c shift=9 hrs.
c/d shift=9hrs.
d shift=9 hrs.
o/d, r/d and a/l= 0 as these values represent time off work.

i need to make a table.
column 1 is employee names.
columns 2-8 are days of the week.
column 9 is total hours.

i have made the table and got the total column doing its thing but how
do i get it so i can input a letter such as C (which is the 9 hr shift)
and have the total calculated based on the number that the letter C
represents?

thanks for any help you guys can give me and happy new year :)
 
Chunky,

Not sure which is being added but here is a starter. Assuming the A,C, etc
are in B2:B6, use

=SUM((B2:B6="A")*9,(B2:B6="C")*8)

This is an array formula, so enter with Ctrl-Shift-Enter. Extend to include
the other codes.

This is none too neat, and I think (hope) there will be better solutions,
but I offer it in case, and await other responses as well.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Chunky:

Bob's solution works well, but I believe you want to sum
across the columns instead of the rows. I think the
formula should be:

=SUM((B2:H2="A")*9,(B2:H2="B")*11)

Extended outward for the other combinations.

Mike.
 
Chunky

Can't come up with anything much better but the COUNTIF
function does not have to be entered as an Array formula.

=COUNTIF(B9:B13,"a")*8+COUNTIF(B9:B13,"b")*11+COUNTIF
(B9:B13,"c")*9+COUNTIF(B9:B13,"c/d")*9+COUNTIF(B9:B13,"d")
*9

Does any one know how to group c, c/d and d together? This
would simplify the formula.

Total columns for daily totals and rows for employees
totals. Absences calculated separately?

Regards
Peter
 
Actually, this is a slightly condensed version

=COUNTIF(test,"a")*8+COUNTIF(test,"b")*11+(COUNTIF
(test,"c")+COUNTIF(test,"c/d")+COUNTIF(test,"d"))*9

where test is a range name.
 
Peter,

As COUNTIF takes wildcards, you could shorten this to

=COUNTIF(test,"a")*8+COUNTIF(test,"b")*11+(COUNTIF(test,"c*")+COUNTIF(test,"
d"))*9

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Even better?

=COUNTIF(test,"a")*8+COUNTIF(test,"b")*11+SUMPRODUCT((A1:A5={"c","c/d","d"})
*9)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
And finally?

=SUMPRODUCT((test={"a","b","c","c/d","d"})*{8,11,9,9,9})

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
yowsah!

thanks chaps :D

will pour over this lot first thing monday and see if i cant get m
head round it
 
Back
Top