Fuel Consumption Calc

SOLVED!!!!!!!!!!!!!!!!!!!!!!!!!!




thx for putting in the effort shg
It's appreciated!




I have a spreadsheet where the goal is to calculate the amount of fuel and fuel bottles to take hiking on trips with me

H2= The amount of fuel in oz.'s I am taking on my trip
I2= A calculation of how many 11oz. bottles to take
J2= A calculation of how many 22oz. bottles to take
K2= A calculation of how many 33 oz. bottles to take

Right now i have:
I2= =IF(H2<11.1,CEILING((H2/I4),1),0)
J2= =IF(H2<22.1,CEILING((H2/J4),1),0)
K2= =IF(H2>22,CEILING((H2/K4),1),0)
I4= 11 (11oz. fuel bottle)
J4= 22 (22oz. fuel bottle)
K4= 33 (33oz. fuel bottle)

The problem, besides the fact that I'm unure how to only count J2 if it's between the two other bottle sizes, is that I want it to cycle between the formuals to pick the smallest bottles possible not just isolating the one bottle size because the total ounces falls into my formula
EXAMPLE:
H2=37
Therefore the formula in K2 takes full control and sets its value to '2'

What I want it to do is
EXAMPLE#1:
if H2(the amount of fuel i'm taking) is 37oz. it'll show as following
I2=1 (4oz. within this bottle)
J2=0 (doesn't count because it's not the logical size to take)
K2=1 (33oz. bottle full)

EXAMPLE#2:
if H2(the amount i'm taking) is 48oz. it'll show as following
I2=0 (doesn't count because it's not the logical size to take)
J2=1 (15oz. within this bottle)
K2=1 (33oz. bottle full)

Welcome to the forum.

Is the problem you're describing equivalent to making change with coins of various values?

Please Login or Register  to view this content.

I'm not sure if that'd work for my spreadsheet
looks like it needs multiple cell values instead of all from one cell

I've attatched the section of the spreadsheet relevant to this
I worked on it for hours last night and got close
but it still needs a few modifications to a formula or two
I'm sure i'm just doing something really simple wrong

Although I gather there is a much simpler way to do it then I've tried


OOO SOLVED IT MYSELF

Thnx for trying to help! I'm sure i'll need a lot more to come

don't forget to make your thread [solved]

How about posting details of how you solved your problem

Couldn't figure out how to make the thread solved =[

As for showing how I did it
It's a mess and only works with my entire spreadsheet linked to it now
it was just a lot of messing around with >'s and <'s =]