Caculation difficulties using linked sheets

  • Thread starter Thread starter Gunjani
  • Start date Start date
G

Gunjani

From WorksheetA I have created a summary Table in SheetB using the
following Formula:
=SUMPRODUCT(('SheetA'!$A$26:$A$147=' SheetB'!$A3)*('4B'!$B$26:$BE$147='
SheetB'!C$1)).

Now in SheetC I wish to use the values in this table to multiply with a
cell 'S5' sheet D (which is in Hours and min)
=' SHEETB'!C$3*'SHEETD'!$S$5

However it doesn't produce the correct results. e.g 7*7:10 = 50:10
(50hrs 10min) but I receive 2:10, and 4*7:10 = 28:40 but I get 4:40.

Where am I going wrong?

Is the formula (=' SHEETB'!C$3*'SHEETD'!$S$5) incorrect or the format
setup in SheetC ?
--
Many Thanks

Gunjani
Friendship improves happiness, and abates misery, by
doubling our joy, and dividing our grief.
-- Joseph Addison
 
yunus786 said:
From WorksheetA I have created a summary Table in SheetB using the
following Formula:
=SUMPRODUCT(('SheetA'!$A$26:$A$147=' SheetB'!$A3)*('4B'!$B$26:$BE$147='
SheetB'!C$1)).

Now in SheetC I wish to use the values in this table to multiply with a
cell 'S5' sheet D (which is in Hours and min)
=' SHEETB'!C$3*'SHEETD'!$S$5

However it doesn't produce the correct results. e.g 7*7:10 = 50:10
(50hrs 10min) but I receive 2:10, and 4*7:10 = 28:40 but I get 4:40.
Click to expand...

I 've realised it's the formatting Number but I don't know how to
correct it tried Format/number/custom, hh:mmm but still produces above
results!!!



--
Many Thanks

Gunjani
At age 4 success is . . . not peeing in your pants.
At age 12 success is . . . having friends.
At age 16 success is . . . having a drivers license.
At age 20 success is . . . having sex.
At age 35 success is . . .having money.
At age 50 success is . . . having money.
At age 60 success is . . . having sex.
At age 70 success is . . . having a drivers license.
At age 75 success is . . having friends.
At age 80 success is . . . not peeing in your pants.
 
The problem is that the way you have formatted the time allows only 24 hours. Use the format cell time and the example which says 37:30:55. That should wor

----- Gunjani wrote: ----

yunus78 said:
From WorksheetA I have created a summary Table in SheetB using the
following Formula
=SUMPRODUCT(('SheetA'!$A$26:$A$147=' SheetB'!$A3)*('4B'!$B$26:$BE$147='
SheetB'!C$1))
cell 'S5' sheet D (which is in Hours and min
=' SHEETB'!C$3*'SHEETD'!$S$
(50hrs 10min) but I receive 2:10, and 4*7:10 = 28:40 but I get 4:40
Click to expand...

I 've realised it's the formatting Number but I don't know how to
correct it tried Format/number/custom, hh:mmm but still produces above
results!!



--
Many Thank

Gunjan
At age 4 success is . . . not peeing in your pants
At age 12 success is . . . having friends
At age 16 success is . . . having a drivers license
At age 20 success is . . . having sex
At age 35 success is . . .having money
At age 50 success is . . . having money
At age 60 success is . . . having sex
At age 70 success is . . . having a drivers license
At age 75 success is . . having friends
At age 80 success is . . . not peeing in your pants
 
The problem is that the way you have formatted the time allows only 24 hours. Use the format cell time and the example which says 37:30:55. That should work
Click to expand...


Tried it but doesn't work
--
Many Thanks

Gunjani
"Every man is afraid of something. That is how
you know he loves you; when he is afraid of
losing you."
 
Hi
just format the target cell with the custom format
[hh]:mm

not the [] bracktes. They prevent the rollover after 24 hours
 
Hi
this won't help :-)
You'll need a custom format like
[hh]:mm:ss
for this

--
Regards
Frank Kabel
Frankfurt, Germany

The problem is that the way you have formatted the time allows only
24 hours. Use the format cell time and the example which says
37:30:55. That should work

----- Gunjani wrote: -----


SheetB'!$A3)*('4B'!$B$26:$BE$147=' > SheetB'!C$1)).
multiply with a > cell 'S5' sheet D (which is in Hours and min)
50:10 > (50hrs 10min) but I receive 2:10, and 4*7:10 = 28:40 but
I get 4:40. >

I 've realised it's the formatting Number but I don't know how to
correct it tried Format/number/custom, hh:mmm but still produces
above results!!!



--
Many Thanks

Gunjani
At age 4 success is . . . not peeing in your pants.
At age 12 success is . . . having friends.
At age 16 success is . . . having a drivers license.
At age 20 success is . . . having sex.
At age 35 success is . . .having money.
At age 50 success is . . . having money.
At age 60 success is . . . having sex.
At age 70 success is . . . having a drivers license.
At age 75 success is . . having friends.
At age 80 success is . . . not peeing in your
Click to expand...
pants.
 
Hi
this won't help :-)
You'll need a custom format like
[hh]:mm:ss
for this

--
Regards
Frank Kabel
Frankfurt, Germany

The problem is that the way you have formatted the time allows only
24 hours. Use the format cell time and the example which says
37:30:55. That should work

----- Gunjani wrote: -----


SheetB'!$A3)*('4B'!$B$26:$BE$147=' > SheetB'!C$1)).
multiply with a > cell 'S5' sheet D (which is in Hours and min)
50:10 > (50hrs 10min) but I receive 2:10, and 4*7:10 = 28:40 but
I get 4:40. >

I 've realised it's the formatting Number but I don't know how to
correct it tried Format/number/custom, hh:mmm but still produces
above results!!!



--
Many Thanks

Gunjani
At age 4 success is . . . not peeing in your pants.
At age 12 success is . . . having friends.
At age 16 success is . . . having a drivers license.
At age 20 success is . . . having sex.
At age 35 success is . . .having money.
Click to expand...
Click to expand...
Thanks it works but Another sticking point....

I wish to now SUM the values in [hh]:min format to an integar 14 (which
equivalent to 14 hrs) the result is #value.
I've tried to Format the integar 14 as [hh]:min but I get 336:00,
incidently in the above format 7 = 168:00, 49=1176:00.
Any suggestions
 
Hi
sum the values and multiply the sum with 24
e.g.
=SUM(E1:E20)*24
and format this cell as number

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
this won't help :-)
You'll need a custom format like
[hh]:mm:ss
for this

--
Regards
Frank Kabel
Frankfurt, Germany

The problem is that the way you have formatted the time allows only
24 hours. Use the format cell time and the example which says
37:30:55. That should work

----- Gunjani wrote: -----

From WorksheetA I have created a summary Table in SheetB
using the > following Formula:
=SUMPRODUCT(('SheetA'!$A$26:$A$147='
SheetB'!$A3)*('4B'!$B$26:$BE$147=' > SheetB'!C$1)).
Now in SheetC I wish to use the values in this table to
multiply with a > cell 'S5' sheet D (which is in Hours and min)
=' SHEETB'!C$3*'SHEETD'!$S$5
However it doesn't produce the correct results. e.g 7*7:10 =
50:10 > (50hrs 10min) but I receive 2:10, and 4*7:10 = 28:40
but I get 4:40. >

I 've realised it's the formatting Number but I don't know how
to correct it tried Format/number/custom, hh:mmm but still
produces above results!!!



--
Many Thanks

Gunjani
At age 4 success is . . . not peeing in your
pants. At age 12 success is . . . having friends.
At age 16 success is . . . having a drivers
license. At age 20 success is . . . having sex.
At age 35 success is . . .having money.
Click to expand...
Click to expand...
Thanks it works but Another sticking point....

I wish to now SUM the values in [hh]:min format to an integar 14
(which equivalent to 14 hrs) the result is #value.
I've tried to Format the integar 14 as [hh]:min but I get 336:00,
incidently in the above format 7 = 168:00, 49=1176:00.
Any suggestions
Click to expand...
 
Back
Top