Formula gone bonkers!
 

Hi All

I have a spreadsheet with various formula on it taking data from an access
database, with help from here i managed to get the whole thing working except
one column, this column works fine for a bit, then for no apparent reason it
goes back to zero and starts again.

Ill explain:

Im using this formula on one sheet to calculate the total time taken to
repair equipment in a given month on sheet 'with costs'

=SUMPRODUCT(--(TEXT('With costs'!P2:P4991,"mm yyyy")="10 2007"),--('With
costs'!H2:H4991),--('With costs'!E2:E4991="Faulty"))

Column 'P' on the 'with costs' sheet is the date
Column 'H' on the 'with costs' sheet is the time
Column 'E' on the 'with costs' sheet is the text 'Faulty' or other text

All columns are formatted correctly and it works for a while then goes back
to zero, i cant explain why

Alternatively if this doesnt work, i think i can calculate the total time
worked in the month by using the total cost column (as i know the hourly
rate) but i dont know how to do this either lol

e.g. cell 'L12' = £333.11 (total cost per month) cell 'L11' has the total
time worked

How would i format the cells

Thanks very much for any help you have as im completely lost :)

Derek

Formula gone bonkers!
 

If the total time worked is in hours then the total cost is just:

total time * hourly rate

If your time is in Excel date/time format, then you will need to
multiply by 24.

Hope this helps.

Pete

On Oct 15, 4:12 pm, Derek wrote:
Hi All

I have a spreadsheet with various formula on it taking data from an access
database, with help from here i managed to get the whole thing working except
one column, this column works fine for a bit, then for no apparent reason it
goes back to zero and starts again.

Ill explain:

Im using this formula on one sheet to calculate the total time taken to
repair equipment in a given month on sheet 'with costs'

=SUMPRODUCT(--(TEXT('With costs'!P2:P4991,"mm yyyy")="10 2007"),--('With
costs'!H2:H4991),--('With costs'!E2:E4991="Faulty"))

Column 'P' on the 'with costs' sheet is the date
Column 'H' on the 'with costs' sheet is the time
Column 'E' on the 'with costs' sheet is the text 'Faulty' or other text

All columns are formatted correctly and it works for a while then goes back
to zero, i cant explain why

Alternatively if this doesnt work, i think i can calculate the total time
worked in the month by using the total cost column (as i know the hourly
rate) but i dont know how to do this either lol

e.g. cell 'L12' = £333.11 (total cost per month) cell 'L11' has the total
time worked

How would i format the cells

Thanks very much for any help you have as im completely lost :)

Derek

Formula gone bonkers!
 

If your formula is giving a total time, and that is in Excel time format
(such as 13:45), then you'll need to multiply by 24 to get it to hours, and
then by the hourly rate, and format the result as General or Number, not as
Time.
--
David Biddulph

"Derek" wrote in message
...
Hi All

I have a spreadsheet with various formula on it taking data from an access
database, with help from here i managed to get the whole thing working
except
one column, this column works fine for a bit, then for no apparent reason
it
goes back to zero and starts again.

Ill explain:

Im using this formula on one sheet to calculate the total time taken to
repair equipment in a given month on sheet 'with costs'

=SUMPRODUCT(--(TEXT('With costs'!P2:P4991,"mm yyyy")="10 2007"),--('With
costs'!H2:H4991),--('With costs'!E2:E4991="Faulty"))

Column 'P' on the 'with costs' sheet is the date
Column 'H' on the 'with costs' sheet is the time
Column 'E' on the 'with costs' sheet is the text 'Faulty' or other text

All columns are formatted correctly and it works for a while then goes
back
to zero, i cant explain why

Alternatively if this doesnt work, i think i can calculate the total time
worked in the month by using the total cost column (as i know the hourly
rate) but i dont know how to do this either lol

e.g. cell 'L12' = £333.11 (total cost per month) cell 'L11' has the total
time worked

How would i format the cells

Thanks very much for any help you have as im completely lost :)

Derek