Hi guys

KNow you guys can help with this...

I need a formula that will calculate lbs & oz when data is entered:

49.02
35.15
24.13

this needs to take account that 1lb = 16 oz, using decimals it would assume
this once 10 is reached.

I dont want to have the lbs and oz in seperate cells if possible

adding my numbers up manually comes out at 109.14
Hope this makes sense.

--
thanks
Roy

This array* formula does it, Roy:

=SUM(INT(A1:A3))
+INT(SUM(MOD(A1:A3,1))*100/16)+MOD(SUM(MOD(A1:A3,1))*100,16)/100

assuming your numbers are in A1 to A3, so adjust the range to suit.

*An array formula has to be committed using Ctrl-Shift-Enter (CSE)
instead of the usual <Enter. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you subsequently edit/amend the
formula you need to use CSE again.

Hope this helps.

Pete

On Aug 4, 5:11*pm, Roy Gudgeon
wrote:
Hi guys

KNow you guys can help with this...

I need a formula that will calculate lbs & oz when data is entered:

49.02
35.15
24.13

this needs to take account that 1lb = 16 oz, using decimals it would assume
this once 10 is reached.

I dont want to have the lbs and oz in seperate cells if possible

adding my numbers up manually comes out at 109.14
Hope this makes sense.

--
thanks
Roy

Here's a non-array version, i.e. commit with <Enter as usual:

=INT(SUM(A1:A3))+INT(MOD(SUM(A1:A3),1)*100/16)+MOD(MOD(SUM(A1:A3),
1)*100,16)/100

Hope this helps.

Pete

On Aug 4, 5:29*pm, Pete_UK wrote:
This array* formula does it, Roy:

=SUM(INT(A1:A3))
+INT(SUM(MOD(A1:A3,1))*100/16)+MOD(SUM(MOD(A1:A3,1))*100,16)/100

assuming your numbers are in A1 to A3, so adjust the range to suit.

*An array formula has to be committed using Ctrl-Shift-Enter (CSE)
instead of the usual <Enter. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you subsequently edit/amend the
formula you need to use CSE again.

Hope this helps.

Pete

On Aug 4, 5:11*pm, Roy Gudgeon
wrote:



Hi guys

KNow you guys can help with this...

I need a formula that will calculate lbs & oz when data is entered:

49.02
35.15
24.13

this needs to take account that 1lb = 16 oz, using decimals it would assume
this once 10 is reached.

I dont want to have the lbs and oz in seperate cells if possible

adding my numbers up manually comes out at 109.14
Hope this makes sense.

--
thanks
Roy- Hide quoted text -

- Show quoted text -

works a treat Pete

How do you guys know these things !
--
thanks
Roy


"Pete_UK" wrote:

Here's a non-array version, i.e. commit with <Enter as usual:

=INT(SUM(A1:A3))+INT(MOD(SUM(A1:A3),1)*100/16)+MOD(MOD(SUM(A1:A3),
1)*100,16)/100

Hope this helps.

Pete

On Aug 4, 5:29 pm, Pete_UK wrote:
This array* formula does it, Roy:

=SUM(INT(A1:A3))
+INT(SUM(MOD(A1:A3,1))*100/16)+MOD(SUM(MOD(A1:A3,1))*100,16)/100

assuming your numbers are in A1 to A3, so adjust the range to suit.

*An array formula has to be committed using Ctrl-Shift-Enter (CSE)
instead of the usual <Enter. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you subsequently edit/amend the
formula you need to use CSE again.

Hope this helps.

Pete

On Aug 4, 5:11 pm, Roy Gudgeon
wrote:



Hi guys

KNow you guys can help with this...

I need a formula that will calculate lbs & oz when data is entered:

49.02
35.15
24.13

this needs to take account that 1lb = 16 oz, using decimals it would assume
this once 10 is reached.

I dont want to have the lbs and oz in seperate cells if possible

adding my numbers up manually comes out at 109.14
Hope this makes sense.

--
thanks
Roy- Hide quoted text -

- Show quoted text -

Thanks for feeding back, Roy.

I suppose I've been using Excel for about 8 years, and before that
Quattro Pro for several more years, so you tend to pick things up ...

Pete

On Aug 4, 5:53*pm, Roy Gudgeon
wrote:
works a treat Pete

How do you guys know these things !
--
thanks
Roy



"Pete_UK" wrote:
Here's a non-array version, i.e. commit with <Enter as usual:

=INT(SUM(A1:A3))+INT(MOD(SUM(A1:A3),1)*100/16)+MOD(MOD(SUM(A1:A3),
1)*100,16)/100

Hope this helps.

Pete

On Aug 4, 5:29 pm, Pete_UK wrote:
This array* formula does it, Roy:

=SUM(INT(A1:A3))
+INT(SUM(MOD(A1:A3,1))*100/16)+MOD(SUM(MOD(A1:A3,1))*100,16)/100

assuming your numbers are in A1 to A3, so adjust the range to suit.

*An array formula has to be committed using Ctrl-Shift-Enter (CSE)
instead of the usual <Enter. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. If you subsequently edit/amend the
formula you need to use CSE again.

Hope this helps.

Pete

On Aug 4, 5:11 pm, Roy Gudgeon
wrote:

Hi guys

KNow you guys can help with this...

I need a formula that will calculate lbs & oz when data is entered:

49.02
35.15
24.13

this needs to take account that 1lb = 16 oz, using decimals it would assume
this once 10 is reached.

I dont want to have the lbs and oz in seperate cells if possible

adding my numbers up manually comes out at 109.14
Hope this makes sense.

--
thanks
Roy- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -