Calculation with Decimal

[Cmenkedi]

I am using Excel 2007 and I have a chart with measurements associated with
gallons (ie. 1" = 10, 2" = 20, 3" = 25). I want to type in a measurement and
have it calculate the gallons. I know I can use Vlookup, but if I have a
measurement of 1.5" I want it to subtract the two values and multiple by .5
and add that number to the first number. I would also like this to be
available for other decimals besides .5. It may be wishful thinking about
doing this, but I would like to get this set up.
Thanks

 

[T. Valko]

if I have a measurement of 1.5" I want it to subtract the two values

Subtract what two values?

Can you post several examples and what result you expect? Need more detailed
info.

 

[Cmenkedi]

If my measurement is 1.5", 1" = 10 & 2" = 20, so (20 -10)*.5 = 5 then 5 + 10
= 15.
Basically I have a tank that is say 50 inches tall. Each inch equals a
number of gallons, but they are not equally different( 1" = 10gal, 20"
456gal). When I measure the tank to see how full it is, I get a number that
may be 10.66". I would like to type in this number and have it calculate the
value that it equals.

10" = a
11" = b
so 10.66 = ((b-a)*.66)+a

I have a chart for the tank and not how the calculated the chart.

I hope this is enough info.
Thanks

 

[Ron Rosenfeld]

I am using Excel 2007 and I have a chart with measurements associated with
gallons (ie. 1" = 10, 2" = 20, 3" = 25). I want to type in a measurement and
have it calculate the gallons. I know I can use Vlookup, but if I have a
measurement of 1.5" I want it to subtract the two values and multiple by .5
and add that number to the first number. I would also like this to be
available for other decimals besides .5. It may be wishful thinking about
doing this, but I would like to get this set up.
Thanks

It would be easier if you had an equation to describe the results, but from
your limited data, it appears that your tank gets narrower as it gets deeper.

I assume what you want to do is interpolate between measurements. Is that the
case?

If so, you can set up your table as such:

Inches Gallons
1 10
2 20
3 25


Name your ranges. For example, if the above is in F1:G4, your named ranges
might be:

Gallons =Sheet1!$G$2:$G$4
Inches =Sheet1!$F$2:$F$4


Then, with your measurement in A1, this formula should interpolate between any
two measurements:

=TREND(OFFSET(Inches,MATCH(A1,Inches)-1,1,2),
OFFSET(Inches,MATCH(A1,Inches)-1,0,2),A1)

The measurement (m) must be in the range min(inches) <= m < max(inches)

Not knowing exactly what you want to do if m is out of range, I offer the
following.

If you want to handle the situation where m < min(inches) insert a row in the
table where 0 inches corresponds to 0 gallons.

Inches Gallons
0 0
1 10
2 20
3 25


If you want to handle the situation where m = max(inches), you could use a
formula like:

=IF(A1= MAX(Inches),MAX(Gallons),TREND(OFFSET(Inches,MATCH(
A1,Inches)-1,1,2),OFFSET(Inches,MATCH(A1,Inches)-1,0,2),A1))


--ron

 

[Cmenkedi]

Thank you that was what I was looking for.

It would be easier if you had an equation to describe the results, but from
your limited data, it appears that your tank gets narrower as it gets deeper.

I assume what you want to do is interpolate between measurements. Is that the
case?

If so, you can set up your table as such:

Inches Gallons
1 10
2 20
3 25


Name your ranges. For example, if the above is in F1:G4, your named ranges
might be:

Gallons =Sheet1!$G$2:$G$4
Inches =Sheet1!$F$2:$F$4


Then, with your measurement in A1, this formula should interpolate between any
two measurements:

=TREND(OFFSET(Inches,MATCH(A1,Inches)-1,1,2),
OFFSET(Inches,MATCH(A1,Inches)-1,0,2),A1)

The measurement (m) must be in the range min(inches) <= m < max(inches)

Not knowing exactly what you want to do if m is out of range, I offer the
following.

If you want to handle the situation where m < min(inches) insert a row in the
table where 0 inches corresponds to 0 gallons.

Inches Gallons
0 0
1 10
2 20
3 25


If you want to handle the situation where m = max(inches), you could use a
formula like:

=IF(A1= MAX(Inches),MAX(Gallons),TREND(OFFSET(Inches,MATCH(
A1,Inches)-1,1,2),OFFSET(Inches,MATCH(A1,Inches)-1,0,2),A1))


--ron

 

[Ron Rosenfeld]

Thank you that was what I was looking for.

You're welcome. Glad to help. Thanks for the feedback.
--ron