Strange formula problem

[gavin]

Sorry for the vague subject line but I couldn't think how to summarise this
problem in a few words.

I've created a workbook to record how many hours I spend working on various
projects. The workbook consists of a sheet for every week of the year and a
totals sheet. The weekly sheets feed into the totals sheet via SUMIF
formulas. The projects all have a unique code which are listed on the totals
sheets and are entered on each week sheet if I work on that project in that
particular week.

On the totals sheet I have a column to enter how many hours are allocated to
each project and a column which compares this number to the total hours
worked on that project. This is where the anomaly lies.

I have entered some test data. I have listed 5 project codes on the totals
sheet and in week 1 I've entered those 5 codes and have allocated 2 hours to
each project. The totals sheet is showing a total of 2 hours against of the
projects - so far so good.

If I enter "3:00" into the "Hours allocated" column for each row my "Hours
remaining" column shows "1:00" against each project. Similarly, if I
allocate 1 hour then the formula in the "Hours remaining column shows "Job
over by 1 hours". Perfect.

The problem arises when the hours allocated and the hours worked are the
same. In this example when I enter "2:00" into the "Hours allocated" column
some of the rows show "0:00" (which is what I would expect" ) but some show
"Job over by 0:00 hours"! I have checked and double checked and all the
relevant cells have the same formulas and are formatted the same - what
could be wrong?

Just to test, in a spare column I entered the formula =IF(C3-F3, "yes",
"no") - column C is Hour allocated and column F totals data from the weekly
sheets. The rows which show "0:00" return "yes" to this formula and the rows
which show "Job over by 0:00 hours" return "no".

I really don't have a clue how to fix this so any help would be greatly
received.


Regards,



Gavin

 

[Sandy Mann]

It sounds as it you are getting rounding errors. Try this as an experement:

In a new workbook enter 00:01 in A1, and 00:02 in A2. Highlight both cells
and drag down to A60 whereupon it will be showing 01:00. Now enter 01:00 in
B60 and in C60 =A60=B60. You should get FALSE. Re-format both cells as
Number and 16 decimal place and you will see that there is a slight
difference.

Format the cells back to time and then in D60 enter:

=ROUND(A60/TIME(,1,),0)*TIME(,1,)=ROUND(B60/TIME(,1,),0)*TIME(,1,)

which should return TRUE.

Apply this method to you comparisons in the Totals sheet.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[gavin]

It sounds as it you are getting rounding errors. Try this as an
experement:

In a new workbook enter 00:01 in A1, and 00:02 in A2. Highlight both
cells and drag down to A60 whereupon it will be showing 01:00. Now enter
01:00 in B60 and in C60 =A60=B60. You should get FALSE. Re-format both
cells as Number and 16 decimal place and you will see that there is a
slight difference.

Format the cells back to time and then in D60 enter:

=ROUND(A60/TIME(,1,),0)*TIME(,1,)=ROUND(B60/TIME(,1,),0)*TIME(,1,)

which should return TRUE.

Apply this method to you comparisons in the Totals sheet.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Hi Sandy,
I'll give that go tomorrow when I'm in work. It certainly sounds like it
could work! Thanks very much for your time.


Regards,



Gavin

 

[gavin]

It sounds as it you are getting rounding errors. Try this as an
experement:

In a new workbook enter 00:01 in A1, and 00:02 in A2. Highlight both
cells and drag down to A60 whereupon it will be showing 01:00. Now enter
01:00 in B60 and in C60 =A60=B60. You should get FALSE. Re-format both
cells as Number and 16 decimal place and you will see that there is a
slight difference.

Format the cells back to time and then in D60 enter:

=ROUND(A60/TIME(,1,),0)*TIME(,1,)=ROUND(B60/TIME(,1,),0)*TIME(,1,)

which should return TRUE.

Apply this method to you comparisons in the Totals sheet.

Hi Sandy,
The cell that compares the hours allocated column to the hours worked column
has the formula

=IF(ISBLANK(C3);"";IF(F3>C3;"job over
by"&""&TEXT(F3-C3;"[hh]:mm")&""&"hour"&IF(24*(F3-C3)>1;"s";"");TEXT(C3-F3;"[hh]:mm")))

Today I've tried to "integrate" your solution (which works great in the
stand alone situation you described) into this formula but I just couldn't
get it to work - too complex for me! Could you suggest how to do this - I'd
be very grateful!


Regards,


Gavin

 

[Sandy Mann]

This seems to work for me:

=IF(ISBLANK(C3),"",IF(F3=C3,"Job On time",IF(F3>C3,"Job over by
"&TEXT(ROUND((F3-C3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1,"s",""),"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1,"s",""))))

So replacing the commas with semi-colons:

=IF(ISBLANK(C3);"";IF(F3=C3;"Job On time";IF(F3>C3;"Job over by
"&TEXT(ROUND((F3-C3)/TIME(;1;0)*TIME(;1;"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1;"s";"");"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(;1;0)*TIME(;1;"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1;"s";""))))

Should work for you.

I don't think that you need the &"" so I took them out.


--
HTH


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

It sounds as it you are getting rounding errors. Try this as an
experement:

In a new workbook enter 00:01 in A1, and 00:02 in A2. Highlight both
cells and drag down to A60 whereupon it will be showing 01:00. Now enter
01:00 in B60 and in C60 =A60=B60. You should get FALSE. Re-format both
cells as Number and 16 decimal place and you will see that there is a
slight difference.

Format the cells back to time and then in D60 enter:

=ROUND(A60/TIME(,1,),0)*TIME(,1,)=ROUND(B60/TIME(,1,),0)*TIME(,1,)

which should return TRUE.

Apply this method to you comparisons in the Totals sheet.

Hi Sandy,
The cell that compares the hours allocated column to the hours worked
column has the formula

=IF(ISBLANK(C3);"";IF(F3>C3;"job over
by"&""&TEXT(F3-C3;"[hh]:mm")&""&"hour"&IF(24*(F3-C3)>1;"s";"");TEXT(C3-F3;"[hh]:mm")))

Today I've tried to "integrate" your solution (which works great in the
stand alone situation you described) into this formula but I just couldn't
get it to work - too complex for me! Could you suggest how to do this -
I'd be very grateful!


Regards,


Gavin

 

[Sandy Mann]

Sorry I forgot that it was the equals part thatyou were having trouble with.
My formula:

=IF(ISBLANK(C3),"",IF(ROUND(F3/TIME(,1,),0)*TIME(,1,)=ROUND(C3/TIME(,1,),0)*TIME(,1,),"Job
On time",IF(F3>C3,"Job over by
"&TEXT(ROUND((F3-C3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1,"s",""),"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1,"s",""))))

With semi-colons:

=IF(ISBLANK(C3);"";IF(ROUND(F3/TIME(;1;0)*TIME(;1=ROUND(C3/TIME(;1;0)*TIME(;1;"Job
On time";IF(F3>C3;"Job over by
"&TEXT(ROUND((F3-C3)/TIME(;1;0)*TIME(;1;"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1;"s";"");"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(;1;0)*TIME(;1;"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1;"s";""))))


--
HTH


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

This seems to work for me:

=IF(ISBLANK(C3),"",IF(F3=C3,"Job On time",IF(F3>C3,"Job over by
"&TEXT(ROUND((F3-C3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1,"s",""),"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1,"s",""))))

So replacing the commas with semi-colons:

=IF(ISBLANK(C3);"";IF(F3=C3;"Job On time";IF(F3>C3;"Job over by
"&TEXT(ROUND((F3-C3)/TIME(;1;0)*TIME(;1;"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1;"s";"");"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(;1;0)*TIME(;1;"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1;"s";""))))

Should work for you.

I don't think that you need the &"" so I took them out.


--
HTH


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

It sounds as it you are getting rounding errors. Try this as an
experement:

In a new workbook enter 00:01 in A1, and 00:02 in A2. Highlight both
cells and drag down to A60 whereupon it will be showing 01:00. Now
enter 01:00 in B60 and in C60 =A60=B60. You should get FALSE.
Re-format both cells as Number and 16 decimal place and you will see
that there is a slight difference.

Format the cells back to time and then in D60 enter:

=ROUND(A60/TIME(,1,),0)*TIME(,1,)=ROUND(B60/TIME(,1,),0)*TIME(,1,)

which should return TRUE.

Apply this method to you comparisons in the Totals sheet.

Hi Sandy,
The cell that compares the hours allocated column to the hours worked
column has the formula

=IF(ISBLANK(C3);"";IF(F3>C3;"job over
by"&""&TEXT(F3-C3;"[hh]:mm")&""&"hour"&IF(24*(F3-C3)>1;"s";"");TEXT(C3-F3;"[hh]:mm")))

Today I've tried to "integrate" your solution (which works great in the
stand alone situation you described) into this formula but I just
couldn't get it to work - too complex for me! Could you suggest how to do
this - I'd be very grateful!


Regards,


Gavin

 

[gavin]

Sorry I forgot that it was the equals part thatyou were having trouble
with. My formula:

=IF(ISBLANK(C3),"",IF(ROUND(F3/TIME(,1,),0)*TIME(,1,)=ROUND(C3/TIME(,1,),0)*TIME(,1,),"Job
On time",IF(F3>C3,"Job over by
"&TEXT(ROUND((F3-C3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(F3-C3)>1,"s",""),"Job Under by
"&TEXT(ROUND((C3-F3)/TIME(,1,),0)*TIME(,1,),"[hh]:mm")&"
hour"&IF(24*(C3-F3)>1,"s",""))))

Hi Sandy,
Thanks for getting back to me. That works great - although I won't pretend I
actually understand what it's doing I applied conditional formatting
to the hours remaining column to turn the cells red when they were greater
than the total column - and I got the inconsistent results I had with the
formula before. I'm not bothered by that though - I fixed it by removing the
formatting

Thanks again for your great help.


Regards,


Gavin