Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif problem - am I bonkers? | Excel Worksheet Functions |