Prev: i want excel to show the first tab upon opening, not the secon
Next: What is a "line print setting"?
From: boss steve boss on 31 Jan 2010 05:25
i would like to have a column that works in pints and gallons that will add
up at the bottom.
so if i put in 1.4 (1 being a gallon & .4 being pints) + 2.7 (2 being a
gallon & .7 being pints) the answer i need would be 4.3
as there are 8 pints in a gallon

Using Excel 2007
From: "David Biddulph" groups [at] on 31 Jan 2010 05:58
=INT(SUM(INT(A1:A10))+SUM(MOD(A1:A10,1)*10/8))+MOD(SUM(INT(A1:A10))+SUM(MOD(A1:A10,1)*10/8),1)*8/10
as an array formula (Control Shift Enter)

--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/

boss steve wrote:
> i would like to have a column that works in pints and gallons that
> will add up at the bottom.
> so if i put in 1.4 (1 being a gallon & .4 being pints) + 2.7 (2 being
> a gallon & .7 being pints) the answer i need would be 4.3
> as there are 8 pints in a gallon
>
> Using Excel 2007


From: Mike H on 31 Jan 2010 06:00
Hi,

I may have made hard work of this and someone may come up with something
simpler but in the meantime try this ARRAY formula. There musn't be any blank
cells in the range and see below on how to enter an array formula

=SUM(INT(A1:A10))+MOD(SUM(MOD(A1:A10,INT(A1:A10)))*10,8)/10+INT(SUM(MOD(A1:A10,INT(A1:A10)))*10/8)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"boss steve" wrote:

> i would like to have a column that works in pints and gallons that will add
> up at the bottom.
> so if i put in 1.4 (1 being a gallon & .4 being pints) + 2.7 (2 being a
> gallon & .7 being pints) the answer i need would be 4.3
> as there are 8 pints in a gallon
>
> Using Excel 2007
From: "David Biddulph" groups [at] on 31 Jan 2010 06:39
That seems to give a #DIV/0! error if there are any empty cells (or cells
where the number of gallons is zero) in the range.
--
David Biddulph


Mike H wrote:
> Hi,
>
> I may have made hard work of this and someone may come up with
> something simpler but in the meantime try this ARRAY formula. There
> musn't be any blank cells in the range and see below on how to enter
> an array formula
>
> =SUM(INT(A1:A10))+MOD(SUM(MOD(A1:A10,INT(A1:A10)))*10,8)/10+INT(SUM(MOD(A1:A10,INT(A1:A10)))*10/8)
>
> This is an array formula which must be entered by pressing
> CTRL+Shift+Enter 'and not just Enter. If you do it correctly then
> Excel will put curly brackets 'around the formula {}. You can't type
> these yourself. If you edit the formula 'you must enter it again with
> CTRL+Shift+Enter.
>
>
>> i would like to have a column that works in pints and gallons that
>> will add up at the bottom.
>> so if i put in 1.4 (1 being a gallon & .4 being pints) + 2.7 (2
>> being a gallon & .7 being pints) the answer i need would be 4.3
>> as there are 8 pints in a gallon
>>
>> Using Excel 2007


From: Mike H on 31 Jan 2010 07:51
David,

I knew about the Div/0 for blank cells and mentioned it in my response but
never tested it with (say) 0.n values. Thanks for pointing that out.

I was just looking at your formula to understand how you avoided div/0 for
blank cells and see it now.

Your solutions is much better than mine :(
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"David Biddulph" wrote:

> That seems to give a #DIV/0! error if there are any empty cells (or cells
> where the number of gallons is zero) in the range.
> --
> David Biddulph
>
>
> Mike H wrote:
> > Hi,
> >
> > I may have made hard work of this and someone may come up with
> > something simpler but in the meantime try this ARRAY formula. There
> > musn't be any blank cells in the range and see below on how to enter
> > an array formula
> >
> > =SUM(INT(A1:A10))+MOD(SUM(MOD(A1:A10,INT(A1:A10)))*10,8)/10+INT(SUM(MOD(A1:A10,INT(A1:A10)))*10/8)
> >
> > This is an array formula which must be entered by pressing
> > CTRL+Shift+Enter 'and not just Enter. If you do it correctly then
> > Excel will put curly brackets 'around the formula {}. You can't type
> > these yourself. If you edit the formula 'you must enter it again with
> > CTRL+Shift+Enter.
> >
> >
> >> i would like to have a column that works in pints and gallons that
> >> will add up at the bottom.
> >> so if i put in 1.4 (1 being a gallon & .4 being pints) + 2.7 (2
> >> being a gallon & .7 being pints) the answer i need would be 4.3
> >> as there are 8 pints in a gallon
> >>
> >> Using Excel 2007
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: i want excel to show the first tab upon opening, not the secon
Next: What is a "line print setting"?