Codeing + TIME Suming troubles

Cameron · Feb 26, 2004

Bob,

Many thanks for the formula, I made one small amendment with the inclussion
of the INDIRECT command...

{=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3

550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3

550"))+17,99)),0)>TIME(0,0,0))*(IF(ISNUMBER(F
IND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3

550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3

550"))+17,99)),0)<=TIME(0,2,0))),"")}

And is in use from F10:U33.

I have some scripting (very little) in this workbook and wondered if I
should move the above (or anything else for that matter) from the worksheet
level and code it instead?

The link
(http://cam.wadla.com/Cut down copy of PowerUp Costings.xls) is a
cut-down-copy of my original spreadsheet which has now 17 worksheets.

I'm also trying to SUM the 'TOTAL HOURS', but with no success.
The cells (F10:F33)`TIMERANGE` are in the format [hh]:mm, but I'm unable to
SUM / AVERAGE / MIN / MAX the range.

Any Suggestions Apreciated.

Cheers,
Cam

Frank Kabel · Feb 26, 2004

Hi
the problem with your time column is that the entries are not times but
text values. You may add the following to your formulas in this column:
=VALUE(your_existing_formula)
and format the result with [hh]:mm

Bob Phillips · Feb 26, 2004

Morning Cameron,

No, don't use VBA, worksheet formulae should always be quicker than any code
we can craft. I would make the formula easier to understand by adding a few
workbook name.

To do this, first select E10, and the add the names.
The first I would name as StartDates with a RefersTo value of
=INDIRECT("'"&'Control Page'!B13&"'!$A$3:$A$550")
The second named as Details with a RefersTo value of
=INDIRECT("'"&'Control Page'!B13&"'!$D$3:$D$550")
And a third of CheckString with a RefersTo value 0
=("Prepaid IP Hours")
Then change the formula in E10 to

=IF(B10<>"",VALUE(MID(LOOKUP("Totals:",StartDates,Details),1,SUM(LEN(LOOKUP(
"Totals:",StartDates,Details))-6)))," ")
and copy down.
=FINDCheckStriung,Details)

The counting formulae also benefit, as the first in G10 would become
=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(Times),TIMEVALUE(MID(Details,Times+LEN(C
heckString),99)),0)>TIME(0,0,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Details,T
imes+LEN(CheckString),99)),0)<=TIME(0,0,0))),"")
H10 becomes
=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(Times),TIMEVALUE(MID(Details,Times+LEN(C
heckString),99)),0)>TIME(0,2,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Details,T
imes+LEN(CheckString),99)),0)<=TIME(0,5,0))),"")
etc.

I did try and make the time boundaries part of a common formula by putting
the boundaries (0,2,5,10, etc. mins) in F7-V7, making the font white to
hide it, and addin g a reference to these in the formula, thereby having one
formula in F10 which will copy across to V10 and down to V33, but it failed
in column K. If I can track it down I will post that amendment.

--

HTH

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

Cameron said:
Bob,

Many thanks for the formula, I made one small amendment with the inclussion
of the INDIRECT command...

{=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)>TIME(0,0,0))*(IF(ISNUMBER(F
IND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)<=TIME(0,2,0))),"")}

And is in use from F10:U33.

I have some scripting (very little) in this workbook and wondered if I
should move the above (or anything else for that matter) from the worksheet
level and code it instead?

The link
(http://cam.wadla.com/Cut down copy of PowerUp Costings.xls) is a
cut-down-copy of my original spreadsheet which has now 17 worksheets.

I'm also trying to SUM the 'TOTAL HOURS', but with no success.
The cells (F10:F33)`TIMERANGE` are in the format [hh]:mm, but I'm unable to
SUM / AVERAGE / MIN / MAX the range.

Any Suggestions Apreciated.

Cheers,
Cam

Bob Phillips · Feb 26, 2004

What a dummy,

I found the error in the times, so let update it. A simple missing $.

As I said, enter 0 in F7, 2 in G7, 5 in H7, etc up to 30 in V7, and change
the font colour to white.

Then change the formula in G7 to

=IF($B10<>"",SUMPRODUCT((IF(ISNUMBER(Times),TIMEVALUE(MID(Details,Times+LEN(
CheckString),99)),0)>TIME(0,F$7,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Detail
s,Times+LEN(CheckString),99)),0)<=TIME(0,G$7,0))),"")

array entered, and copy down and across.

I think you have more maintainable formulae then.

--

HTH

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

Bob Phillips said:
Morning Cameron,

No, don't use VBA, worksheet formulae should always be quicker than any code
we can craft. I would make the formula easier to understand by adding a few
workbook name.

To do this, first select E10, and the add the names.
The first I would name as StartDates with a RefersTo value of
=INDIRECT("'"&'Control Page'!B13&"'!$A$3:$A$550")
The second named as Details with a RefersTo value of
=INDIRECT("'"&'Control Page'!B13&"'!$D$3:$D$550")
And a third of CheckString with a RefersTo value 0
=("Prepaid IP Hours")
Then change the formula in E10 to

=IF(B10 said:
"Totals:",StartDates,Details))-6)))," ")
and copy down.
=FINDCheckStriung,Details)

The counting formulae also benefit, as the first in G10 would become
heckString),99)),0)>TIME(0,0,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Details,T
imes+LEN(CheckString),99)),0)<=TIME(0,0,0))),"")
H10 becomes
heckString),99)),0)>TIME(0,2,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Details,T
imes+LEN(CheckString),99)),0)<=TIME(0,5,0))),"")
etc.

I did try and make the time boundaries part of a common formula by putting
the boundaries (0,2,5,10, etc. mins) in F7-V7, making the font white to
hide it, and addin g a reference to these in the formula, thereby having one
formula in F10 which will copy across to V10 and down to V33, but it failed
in column K. If I can track it down I will post that amendment.

--

HTH

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

Cameron said:

Bob,

Many thanks for the formula, I made one small amendment with the inclussion
of the INDIRECT command...

{=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(FIND("Prepaid IP

Click to expand...

Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:

D550"),FIND("Prepaid IP

Click to expand...

Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)>TIME(0,0,0))*(IF(ISNUMBER(F

IND("Prepaid IP

Click to expand...

Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:

D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)<=TIME(0,2,0))),"")}

And is in use from F10:U33.

I have some scripting (very little) in this workbook and wondered if I
should move the above (or anything else for that matter) from the worksheet
level and code it instead?

The link
(http://cam.wadla.com/Cut down copy of PowerUp Costings.xls)

Click to expand...

is

a
cut-down-copy of my original spreadsheet which has now 17 worksheets.

I'm also trying to SUM the 'TOTAL HOURS', but with no success.
The cells (F10:F33)`TIMERANGE` are in the format [hh]:mm, but I'm unable to
SUM / AVERAGE / MIN / MAX the range.

Any Suggestions Apreciated.

Cheers,
Cam

Click to expand...

Cameron · Mar 1, 2004

Frank,
Many thanks for your help - you were absolutely correct.
I noticed the use of VALUE just seconds before seeing your reply.
Cam

Frank Kabel said:
Hi
the problem with your time column is that the entries are not times but
text values. You may add the following to your formulas in this column:
=VALUE(your_existing_formula)
and format the result with [hh]:mm

--
Regards
Frank Kabel
Frankfurt, Germany

Bob,

Many thanks for the formula, I made one small amendment with the
inclussion of the INDIRECT command...

{=IF(B10<>"",SUMPRODUCT((IF(ISNUMBER(FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"
'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)>TIME(0,0,0))*(IF(ISNUM
BER(F
IND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"
'!D3:
D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)<=TIME(0,2,0))),"")}

And is in use from F10:U33.

I have some scripting (very little) in this workbook and wondered if I
should move the above (or anything else for that matter) from the
worksheet level and code it instead?

The link
(http://cam.wadla.com/Cut down copy of PowerUp Costings.xls)
is a cut-down-copy of my original spreadsheet which has now 17
worksheets.

I'm also trying to SUM the 'TOTAL HOURS', but with no success.
The cells (F10:F33)`TIMERANGE` are in the format [hh]:mm, but I'm
unable to SUM / AVERAGE / MIN / MAX the range.

Any Suggestions Apreciated.

Cheers,
Cam

Click to expand...

Cameron · Mar 1, 2004

Bob,

Many thanks for your assistance with my inexperience with forumlas.

I was traveling nicely implementing portions of the formula as I observed
the references you'd provided and seeing the nett results. When I
implementing the `Times` reference (last reference I included) I was
initially getting a #VALUE error. What I didn't realise was that I wasn't
entering it as an array formula {}, hence the error - silly me and what a
time to forget.

The resulting formula is slightly different due to loss of references ... In
cell {H10} array formula ...

=IF($B10<>"",SUMPRODUCT((IF(ISNUMBER(Times),TIMEVALUE(MID(Details,Times+LEN(
CheckString),99)),0)>TIME(0,G$7,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Detail
s,Times+LEN(CheckString),99)),0)<=TIME(0,H$7,0))),"")

I also made the suttle changes to the following `names` ...

In `Details` =INDIRECT("'"&'Control Page'!$B10&"'!$D3:$D$550")

In `StartDates` =INDIRECT("'"&'Control Page'!$B10&"'!$A$3:$A$550")

In `Times` =FIND("Prepaid IP Hours",INDIRECT("'"&'Control
Page'!$B10&"'!D3

550"))

I have enjoy what I've learnt and thank you for your patience and
experience.

Regards,

Cameron

Bob Phillips said:
What a dummy,

I found the error in the times, so let update it. A simple missing $.

As I said, enter 0 in F7, 2 in G7, 5 in H7, etc up to 30 in V7, and change
the font colour to white.

Then change the formula in G7 to

CheckString),99)),0)>TIME(0,F$7,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Detail
s,Times+LEN(CheckString),99)),0)<=TIME(0,G$7,0))),"")

array entered, and copy down and across.

I think you have more maintainable formulae then.

--

HTH

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

Bob Phillips said:

Morning Cameron,

No, don't use VBA, worksheet formulae should always be quicker than any code
we can craft. I would make the formula easier to understand by adding a few
workbook name.

To do this, first select E10, and the add the names.
The first I would name as StartDates with a RefersTo value of
=INDIRECT("'"&'Control Page'!B13&"'!$A$3:$A$550")
The second named as Details with a RefersTo value of
=INDIRECT("'"&'Control Page'!B13&"'!$D$3:$D$550")
And a third of CheckString with a RefersTo value 0
=("Prepaid IP Hours")
Then change the formula in E10 to

Click to expand...

=IF(B10 said:

"Totals:",StartDates,Details))-6)))," ")
and copy down.
=FINDCheckStriung,Details)

The counting formulae also benefit, as the first in G10 would become

Click to expand...

heckString),99)),0)>TIME(0,0,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Details,T

imes+LEN(CheckString),99)),0)<=TIME(0,0,0))),"")
H10 becomes

Click to expand...

heckString),99)),0)>TIME(0,2,0))*(IF(ISNUMBER(Times),TIMEVALUE(MID(Details,T

imes+LEN(CheckString),99)),0)<=TIME(0,5,0))),"")
etc.

I did try and make the time boundaries part of a common formula by putting
the boundaries (0,2,5,10, etc. mins) in F7-V7, making the font white to
hide it, and addin g a reference to these in the formula, thereby having one
formula in F10 which will copy across to V10 and down to V33, but it failed
in column K. If I can track it down I will post that amendment.

--

HTH

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

Click to expand...

Hours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)>TIME(0,0,0))*(IF(ISNUMBER(FHours",INDIRECT("'"&B10&"'!D3550"))),TIMEVALUE(MID(INDIRECT("'"&B10&"'!D3:

D550"),FIND("Prepaid IP
Hours",INDIRECT("'"&B10&"'!D3550"))+17,99)),0)<=TIME(0,2,0))),"")}

And is in use from F10:U33.

I have some scripting (very little) in this workbook and wondered if I
should move the above (or anything else for that matter) from the worksheet
level and code it instead?

The link
(http://cam.wadla.com/Cut down copy of PowerUp Costings.xls)

Click to expand...

is

a
cut-down-copy of my original spreadsheet which has now 17 worksheets.

I'm also trying to SUM the 'TOTAL HOURS', but with no success.
The cells (F10:F33)`TIMERANGE` are in the format [hh]:mm, but I'm

Click to expand...

unable
to

SUM / AVERAGE / MIN / MAX the range.

Any Suggestions Apreciated.

Cheers,
Cam

Click to expand...

Click to expand...