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

>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.

--
Biff
Microsoft Excel MVP


"Cmenkedi" <(E-Mail Removed)> wrote in message
news:323EFC89-8EAA-4957-9C19-(E-Mail Removed)...
>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
>

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


"T. Valko" wrote:

> >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.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Cmenkedi" <(E-Mail Removed)> wrote in message
> news:323EFC89-8EAA-4957-9C19-(E-Mail Removed)...
> >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
> >
>
>
>

On Thu, 3 Jan 2008 07:40:02 -0800, Cmenkedi
<(E-Mail Removed)> wrote:

>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

Thank you that was what I was looking for.

"Ron Rosenfeld" wrote:

> On Thu, 3 Jan 2008 07:40:02 -0800, Cmenkedi
> <(E-Mail Removed)> wrote:
>
> >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
>

On Thu, 3 Jan 2008 12:55:01 -0800, Cmenkedi
<(E-Mail Removed)> wrote:

>Thank you that was what I was looking for.

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