Returning a numeric value for text

[sbg1275]

Hi, not sure if this can be done but here goes:

I am creating a attendance spreadsheet (oh such fun) and need to add staff holidays, and also TOIL to the list.

At the end of the row (for each member of staff) I need to add how many days holiday has been taken, and in a seperate column how many days TOIL.

I want to keep the sheet very simple (the CEO needs to be able to understand it!) and therefore want to be able to use the simple method of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This would be easy as I would use the COUNTIF function but ...

... staff also take half day holidays and TOIL.

So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then adding the range together?

Hope this all makes sense - beginning to think that it just can't be done.

Cheers

[swatsp0p]

Use COUNTIF to do this. Your formula should look like this, adjusting the ranges to meet your data table:

=COUNTIF(B2:Z2,"=H")+COUNTIF(B2:Z2,"=H2")/2

Note that I used H2 for the half days and divided the count of those entries by 2. Therefore, if your data range contains:

H, H, H2, H, H2 the result will be 4 (3 H+2 H2/2)

And use =COUNTIF(B2:Z2,"=T")")+COUNTIF(B2:Z2,"=T2")/2 to count the TOIL days (what does TOIL stand for?) .

HTH

Bruce

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[sbg1275]

Hey guys,

Thanks for the replies. I've gone with Bruce's slightly less complicated route. I was so hung up on stating somewhere that H2 = 0.5 that I totally forgot my basic division maths!!

I've chucked that formula into the worksheet and it is definitely going to work. Yippee.
The only problem now will be if some bu**er decides to take the whole day off but half and half holiday and TOIL! Think I will cross that bridge when it comes to it. May have to have another code such as HT..

Oh, and TOIL means Time Off In Lieu, as we don't get paid overtime at my company.

Enough waffle from me - thanks so much for the help.
Can go and impress my boss now!

Cheers
S

[swatsp0p]

For those bu**ers that do the 1/2 and 1/2 H and T, we simply need to add the HT calculation into both formulas, as such:

=COUNTIF(B2:Z2,"=H")+COUNTIF(B2:Z2,"=H2")/2+COUNTIF(B2:Z2,"HT")/2

and

=COUNTIF(B2:Z2,"=T")")+COUNTIF(B2:Z2,"=T2")/2+COUNTIF(B2:Z2,"HT")/2

should work nicely for you.

Cheers!

Bruce

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>

[Aladin Akyurek]

Example setup:

=SUMPRODUCT(LOOKUP(A2:E2,{0,0;"",0;"H",1;"H1",0.5;"T",2;"T1",0.4}))

The last bit between { and } is actually a table. If that table is in a
range, say in L2:M7, laid out like this:

L2: 0
L3: =""
L4: H
L5: H1
L6: T
L7: T1

M2: 0
M3: 0
M4: 1
M5: 0.5
M6: 2
M7: 0.4

and this range is named as STable, then:

=SUMPRODUCT(LOOKUP(A2:E2,STable))

Note that STable is sorted on its first column in ascending order.

Note also that A2:E2, the range of interest, should not house any symbol
which is not in STable.

sbg1275 wrote:
> Hi, not sure if this can be done but here goes:
>
> I am creating a attendance spreadsheet (oh such fun) and need to add
> staff holidays, and also TOIL to the list.
>
> At the end of the row (for each member of staff) I need to add how many
> days holiday has been taken, and in a seperate column how many days
> TOIL.
>
> I want to keep the sheet very simple (the CEO needs to be able to
> understand it!) and therefore want to be able to use the simple method
> of "H" or "T" to indicate Holiday or TOIL - simple enough so far. This
> would be easy as I would use the COUNTIF function but ...
>
> .. staff also take half day holidays and TOIL.
>
> So, is there anyway at all of stating that H1/2 = 0.5 and H=1 and then
> adding the range together?
>
> Hope this all makes sense - beginning to think that it just can't be
> done.
>
> Cheers
>
>