how do i program alphabet letters to represent numerical values?

[Guest]

i am trying to create an off duty rota of which to give to colleages, i need
to display their shifts in alphabetical letters ('LD' long day) which denote
a numerical value (12) for hours of shift, in order to have a cumulative
total, please assist.

 

[Roger Govier]

Hi Jo

Not really sure what you are after here.
Perhaps LD is 12 hours, ND (Normal Day) is 8 hours SD (Short Day) is 5
hours
Maybe, if LD etc is in column B
=COUNTIF(B:B,"LD")*12 + COUNTIF(B:B,"ND")*8 + COUNTIF(B,"SD)*5

Adjust to suit.

 

[Dallman Ross]

In <[email protected]>, Roger Govier

Not really sure what you are after here.
Perhaps LD is 12 hours, ND (Normal Day) is 8 hours SD (Short Day)
is 5 hours
Maybe, if LD etc is in column B
=COUNTIF(B:B,"LD")*12 + COUNTIF(B:B,"ND")*8 + COUNTIF(B,"SD)*5

Another approach is with LOOKUP and vectors. I use the following,
for example, for helping to find part of the symbol for a call
option (security derivative) based on the month the call expires:

=LOOKUP(C1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP";"D","H","L","B","A","G","F","C","E","K","J","I"})

I'm posting my example partly to help me point out that the data
need to be in order, be that numerical or alphabetical (string
order), for results to work. So, continuing with Roger's
example of "ND" and "SD" for "normal" and "short" days in
addition to the original poster's "LD", we could have:

=LOOKUP(C1,{"LD","ND","SD";12,8,5})

That should do it, I think.

Dallman Ross

 

[JERRYDES]

Hi jo,
I think Roger's solution is close to the mark. I have used the following
variation on it'-

Try the following formula into C3 and copy it down column C

=COUNTIF(B2,"LD")*12+COUNTIF(B2,"ND")*8+COUNTIF(B2,"SD")*5

Sunday 08/10/2006 LD 12 Total 112
Monday 09/10/2006 ND 8
Tuesday 10/10/2006 SD 5
Wednesday 11/10/2006 LD 12
Thursday 12/10/2006 ND 8
Friday 13/10/2006 SD 5
Saturday 14/10/2006 LD 12
Sunday 15/10/2006 ND 8
Monday 16/10/2006 SD 5
Tuesday 17/10/2006 LD 12
Wednesday 18/10/2006 ND 8
Thursday 19/10/2006 SD 5
Friday 20/10/2006 LD 12



Best regards
Jerrydes

 

[Roger Govier]

Hi Jo

Apologies
Typo, mistakenly typed a D in place of B in the last Countif

=COUNTIF(B:B,"LD")*12 + COUNTIF(B:B,"ND")*8 + COUNTIF(B,"SD)*5

should of course be
=COUNTIF(B:B,"LD")*12 + COUNTIF(B:B,"ND")*8 + COUNTIF(B:B,"SD)*5