Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i work i pints and gallons
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i work i pints and gallons
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i work i pints and gallons
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i work i pints and gallons
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i work i pints and gallons
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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i work i pints and gallons
Somewhat late, but I spend time thinking about this when I should have been
otherwise occupied in church this morning =SUM(INT(A1:A10))+INT(SUM(MOD(A1:A10,1))*10/8)+MOD(SUM(MOD(A1:A10,1)),0.8) Enter as array formula (CTRL+SHIFT+ENTER) Just a bit shorter than David's best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "boss steve" <boss wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How many gallons? | Excel Discussion (Misc queries) | |||
Convert Square Feet to Gallons | Excel Worksheet Functions | |||
Gallons and % | Excel Discussion (Misc queries) | |||
Excel 2003, how to convert gallons to cubic feet? | Excel Worksheet Functions | |||
uk gallons | New Users to Excel |