Hello.

Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door. It is really to
determine how large of a tub to put in for a person based on their
size.

I'm guessing you will need information on the dimensions of the tub?
The client mentioned water displacement, and the numbers that follow

Here is what he told me he found out about water displacement

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I'm not sure where to EVEN start with this. Am I over my head, or do I
even have enough information?

Thanks for the help.

Displacement generally refers to the volume of water displaced when an object
is put in its container.

Given the numbers you have below, I would start with the relation 1001bs
displaces .5 cubic feet of water. Presumably 200lbs discplaces a cubic foot
of water, which, by your relation 8 gallons = 1 cubic foot, you know that a
200 lb person displaces 8 gallons of water.

You have the information you need; you just need to work through it. I
don't know what the dimensions of a tub would get you, and I'm not sure why
you would need a person's height.

Dave
--
Brevity is the soul of wit.


"Darryl" wrote:

Hello.

Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door. It is really to
determine how large of a tub to put in for a person based on their
size.

I'm guessing you will need information on the dimensions of the tub?
The client mentioned water displacement, and the numbers that follow

Here is what he told me he found out about water displacement

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I'm not sure where to EVEN start with this. Am I over my head, or do I
even have enough information?

Thanks for the help.

To be more succinct:

If 100 lbs displaces 1/2 cubic foot of water, and
1/2 cubic foot of water equals 4 gallons, then the ratio 100 lbs : 1/2 cubic
foot : 4 gallons holds.

So you could construct a table with the column headings Weight | Volume,
cubic feet | volume, liquid and populate it according to the above ratio:

A 50 pound person would displace 1/4 of a cubic foot, which is 2 gallons of
water.

A 200 pound person would displace 1 cubic foot of water, which is 8 gallons
of water
--
Brevity is the soul of wit.


"Dave F" wrote:

Displacement generally refers to the volume of water displaced when an object
is put in its container.

Given the numbers you have below, I would start with the relation 1001bs
displaces .5 cubic feet of water. Presumably 200lbs discplaces a cubic foot
of water, which, by your relation 8 gallons = 1 cubic foot, you know that a
200 lb person displaces 8 gallons of water.

You have the information you need; you just need to work through it. I
don't know what the dimensions of a tub would get you, and I'm not sure why
you would need a person's height.

Dave
--
Brevity is the soul of wit.


"Darryl" wrote:

Hello.

Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door. It is really to
determine how large of a tub to put in for a person based on their
size.

I'm guessing you will need information on the dimensions of the tub?
The client mentioned water displacement, and the numbers that follow

Here is what he told me he found out about water displacement

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I'm not sure where to EVEN start with this. Am I over my head, or do I
even have enough information?

Thanks for the help.

having established that, where will the displaced water go? Will it spill
over the side or is the purpose of the question to decide how much ullage
must be left in the tub prior to someone having a bath. I suggest a bund
around the tub with a mimimum capacity of 110% of the tub capacity.

1000kg of water @ 21c has a volume of 1m3. What is the volume of 1000kg of
water @ 40c and what is the effect of body structure on body volume. Discuss.

"Dave F" wrote:

To be more succinct:

If 100 lbs displaces 1/2 cubic foot of water, and
1/2 cubic foot of water equals 4 gallons, then the ratio 100 lbs : 1/2 cubic
foot : 4 gallons holds.

So you could construct a table with the column headings Weight | Volume,
cubic feet | volume, liquid and populate it according to the above ratio:

A 50 pound person would displace 1/4 of a cubic foot, which is 2 gallons of
water.

A 200 pound person would displace 1 cubic foot of water, which is 8 gallons
of water
--
Brevity is the soul of wit.


"Dave F" wrote:

Displacement generally refers to the volume of water displaced when an object
is put in its container.

Given the numbers you have below, I would start with the relation 1001bs
displaces .5 cubic feet of water. Presumably 200lbs discplaces a cubic foot
of water, which, by your relation 8 gallons = 1 cubic foot, you know that a
200 lb person displaces 8 gallons of water.

You have the information you need; you just need to work through it. I
don't know what the dimensions of a tub would get you, and I'm not sure why
you would need a person's height.

Dave
--
Brevity is the soul of wit.


"Darryl" wrote:

Hello.

Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door. It is really to
determine how large of a tub to put in for a person based on their
size.

I'm guessing you will need information on the dimensions of the tub?
The client mentioned water displacement, and the numbers that follow

Here is what he told me he found out about water displacement

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I'm not sure where to EVEN start with this. Am I over my head, or do I
even have enough information?

Thanks for the help.

The effect of body structure on volume is that a person who is more muscular
than normal will have a higher density than a fatter person. Therefore a
muscular person who is the same weight as a fat person will take up less
volume.

But they will displace the same amount of water because they weigh the same.
I'm not sure what body structure differences have to do with your question?

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

having established that, where will the displaced water go? Will it spill
over the side or is the purpose of the question to decide how much ullage
must be left in the tub prior to someone having a bath. I suggest a bund
around the tub with a mimimum capacity of 110% of the tub capacity.

1000kg of water @ 21c has a volume of 1m3. What is the volume of 1000kg of
water @ 40c and what is the effect of body structure on body volume. Discuss.

"Dave F" wrote:

To be more succinct:

If 100 lbs displaces 1/2 cubic foot of water, and
1/2 cubic foot of water equals 4 gallons, then the ratio 100 lbs : 1/2 cubic
foot : 4 gallons holds.

So you could construct a table with the column headings Weight | Volume,
cubic feet | volume, liquid and populate it according to the above ratio:

A 50 pound person would displace 1/4 of a cubic foot, which is 2 gallons of
water.

A 200 pound person would displace 1 cubic foot of water, which is 8 gallons
of water
--
Brevity is the soul of wit.


"Dave F" wrote:

Displacement generally refers to the volume of water displaced when an object
is put in its container.

Given the numbers you have below, I would start with the relation 1001bs
displaces .5 cubic feet of water. Presumably 200lbs discplaces a cubic foot
of water, which, by your relation 8 gallons = 1 cubic foot, you know that a
200 lb person displaces 8 gallons of water.

You have the information you need; you just need to work through it. I
don't know what the dimensions of a tub would get you, and I'm not sure why
you would need a person's height.

Dave
--
Brevity is the soul of wit.


"Darryl" wrote:

Hello.

Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door. It is really to
determine how large of a tub to put in for a person based on their
size.

I'm guessing you will need information on the dimensions of the tub?
The client mentioned water displacement, and the numbers that follow

Here is what he told me he found out about water displacement

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I'm not sure where to EVEN start with this. Am I over my head, or do I
even have enough information?

Thanks for the help.

dave,

Agree totally about the volumetric differences between people of the same
weight and the amount of water they will displace. That, added to to the
varying volume of water depending on salinity and temperature would make any
meaningful calculation based upon the information provided a bit unhelpful.
The bathroom floor will get wet.

Mike

"Dave F" wrote:

The effect of body structure on volume is that a person who is more muscular
than normal will have a higher density than a fatter person. Therefore a
muscular person who is the same weight as a fat person will take up less
volume.

But they will displace the same amount of water because they weigh the same.
I'm not sure what body structure differences have to do with your question?

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

having established that, where will the displaced water go? Will it spill
over the side or is the purpose of the question to decide how much ullage
must be left in the tub prior to someone having a bath. I suggest a bund
around the tub with a mimimum capacity of 110% of the tub capacity.

1000kg of water @ 21c has a volume of 1m3. What is the volume of 1000kg of
water @ 40c and what is the effect of body structure on body volume. Discuss.

"Dave F" wrote:

To be more succinct:

If 100 lbs displaces 1/2 cubic foot of water, and
1/2 cubic foot of water equals 4 gallons, then the ratio 100 lbs : 1/2 cubic
foot : 4 gallons holds.

So you could construct a table with the column headings Weight | Volume,
cubic feet | volume, liquid and populate it according to the above ratio:

A 50 pound person would displace 1/4 of a cubic foot, which is 2 gallons of
water.

A 200 pound person would displace 1 cubic foot of water, which is 8 gallons
of water
--
Brevity is the soul of wit.


"Dave F" wrote:

Displacement generally refers to the volume of water displaced when an object
is put in its container.

Given the numbers you have below, I would start with the relation 1001bs
displaces .5 cubic feet of water. Presumably 200lbs discplaces a cubic foot
of water, which, by your relation 8 gallons = 1 cubic foot, you know that a
200 lb person displaces 8 gallons of water.

You have the information you need; you just need to work through it. I
don't know what the dimensions of a tub would get you, and I'm not sure why
you would need a person's height.

Dave
--
Brevity is the soul of wit.


"Darryl" wrote:

Hello.

Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door. It is really to
determine how large of a tub to put in for a person based on their
size.

I'm guessing you will need information on the dimensions of the tub?
The client mentioned water displacement, and the numbers that follow

Here is what he told me he found out about water displacement

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I'm not sure where to EVEN start with this. Am I over my head, or do I
even have enough information?

Thanks for the help.

There seems to be an inconsistency in your given data:

63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

1 cubic foot = 8 gallons. 1 gallon = 8 lbs
Therefore 1Cu Ft = 8 x 8 lbs = 64 lbs water

62 lbs of water is displaced by 63 lbs
therefore 64 lbs water is displaced by (63/62) x 64 lbs = 65 lbs

therefore 1 cu ft water is displaced by 65 lbs
but the given data is 100 lbs displaces 1/2 cu ft water!

1 cu ft = (12" x 2.54cm)^3 = 28316.9 cu cm
1 cu cm is almost exactly 1 gram so 1 cu ft water weighs:

(12" x 2.53cm)^3/1000 = 28.3 Kg

1 Kg = 2.204622622 lbs (assuming American and Imperial lbs are the same)
so:
1 cu ft water weighs:
(12 x 2.54cm)^3/1000*2.22046322622 = 62.4 lbs

1 cu ft is therefore displaced by (63/62) x 62.4 = 63.4 lbs

100 lbs therefore displaces (100/63.4) = 1.576 cu ft

Surely then

100lbs displaces ½ cubic feet of water

should be100 lbs displaces 1½ cubic feet of water

quite apart from all the other convolutions.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Darryl wrote:
Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door.

=GALLONS(A1*B1)

where A1 and B1 are the height and weight of the individual.

Okay, just kidding! Obviously this is not an Excel question. You
would do better for yourself if you posted your inquiry to a physics
newsgroup. After someone tells you the correct formula to use, if you
have questions about how to formulate that in Excel, come back here for
answers.

It is really to determine how large of a tub to put in for a person based on their
size.
I'm guessing you will need information on the dimensions of the tub?

Huh!? "In order to determine the dimensions, we need to know the
dimensions"!? Well, perhaps you mean the ratio of length, width and
height.

The client mentioned water displacement, and the numbers that follow
Here is what he told me he found out about water displacement
63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I am not bothering to double-check these facts. But I wonder if your
"client" meant to say that 100 lbs displaces 1 1/2 cu ft. Here is how
I figure that... If a (hypothetical) 63-lb person displaces 62 lbs of
water (surprise!), then a 100-lb person (of the same shape
proportionately) displaces 98 lbs of water (62 * 100 / 63). If 1 gal
of water weighs 8 lbs, then that would be 12 gal (98 / 8). If 8 gal is
1 cu ft, then that would be 1.5 cu ft (12 / 8).

"Of the same shape proportionately" is the key. In order to determine
displacement, it is the volume of the person that matters, not the
person's height and weight per se. I 'spose you could try to estimate
volume based on those parameters. But I know of no formula for that --
even an anthropomorphic estimate.

(If I were pressed, I would estimate hip dimension based on height and
weight, then estimate volume by modeling a person as a cylinder. But
that is really a poor estimate. Height and weight can give you BMI,
which is an indication of obesity. The obesity indication might offer
some guidance in estimating a height-waist ratio. But you really need
hip dimension, IMHO.)

I'm not sure where to EVEN start with this. Am I over my head

No pun intended? ;-)

If the "client's" relationships are correct (given the correction
above) for "the person" (hopefully you mean maximum design parameters)
in question, then given the person's weight in A1, the water
displacement (cu ft) would be (in A2, for example):

=A1 * 62 / 63 / 8 / 8

In my opinion, that gives you very little information to help you
design the tub dimensions. It tells you how much water might be
displaced. But it does not tell you how much water you start with.

You might model the tub as a rectangular solid. (It is really more
like a trapezoidal solid.)
The maximum height (H) of the tub should probably be about 14 inches --
no higher in order to allow a person to step over. The minimum length
(L) of the tub should probably be the person's height (PH) plus a fudge
factor (6 inches or more; much more if you want a two-person tub ;-).
The minimum width (W) should probably be, say, 1.5 to 2 times the width
of the person at the hips (PW).

The minimum amount of water should be enough to cover the person's legs
-- typically more. So you need an estimate of the width of the legs
(PL) and the width of the hips.

Thus, the maximum volume of water (cu ft) is, assuming dimensions are
in inches (in A3):

=L * W * H / 1728

where L=PH+6 and W=1.5*PW at least, subject to real-world constraints.

The minimum volume of water is (in A4):

=L * W * PL / 1728

In some cell, you might write:

=if(A2 + A4 A3 - 4*L*W, "overflow!", "okay")

where 4*L*W represents a maximum water height with 4 inches of the top
of the top.

Caveat: This is just a quick off-hand estimate of things. I have not
bothered to check it for correctness. But hopefully it points you in
the right direction.

wrote:
Darryl wrote:
Excel Newbie here. I've been asked to create a spreadsheet that will
calculate the gallons of water needed based on the Height and weight of
a person going into a walk-in bathtub with a door.

=GALLONS(A1*B1)

where A1 and B1 are the height and weight of the individual.

Okay, just kidding! Obviously this is not an Excel question. You
would do better for yourself if you posted your inquiry to a physics
newsgroup. After someone tells you the correct formula to use, if you
have questions about how to formulate that in Excel, come back here for
answers.

It is really to determine how large of a tub to put in for a person based on their
size.
I'm guessing you will need information on the dimensions of the tub?

Huh!? "In order to determine the dimensions, we need to know the
dimensions"!? Well, perhaps you mean the ratio of length, width and
height.

The client mentioned water displacement, and the numbers that follow
Here is what he told me he found out about water displacement
63 lbs will displace w62lbs of water
100lbs displaces ½ cubic feet of water
1 gallon weighs 8lbs
8 gallons = 1 cubic foot

I am not bothering to double-check these facts. But I wonder if your
"client" meant to say that 100 lbs displaces 1 1/2 cu ft. Here is how
I figure that... If a (hypothetical) 63-lb person displaces 62 lbs of
water (surprise!), then a 100-lb person (of the same shape
proportionately) displaces 98 lbs of water (62 * 100 / 63). If 1 gal
of water weighs 8 lbs, then that would be 12 gal (98 / 8). If 8 gal is
1 cu ft, then that would be 1.5 cu ft (12 / 8).

"Of the same shape proportionately" is the key. In order to determine
displacement, it is the volume of the person that matters, not the
person's height and weight per se. I 'spose you could try to estimate
volume based on those parameters. But I know of no formula for that --
even an anthropomorphic estimate.

(If I were pressed, I would estimate hip dimension based on height and
weight, then estimate volume by modeling a person as a cylinder. But
that is really a poor estimate. Height and weight can give you BMI,
which is an indication of obesity. The obesity indication might offer
some guidance in estimating a height-waist ratio. But you really need
hip dimension, IMHO.)

I'm not sure where to EVEN start with this. Am I over my head

No pun intended? ;-)

If the "client's" relationships are correct (given the correction
above) for "the person" (hopefully you mean maximum design parameters)
in question, then given the person's weight in A1, the water
displacement (cu ft) would be (in A2, for example):

=A1 * 62 / 63 / 8 / 8

In my opinion, that gives you very little information to help you
design the tub dimensions. It tells you how much water might be
displaced. But it does not tell you how much water you start with.

You might model the tub as a rectangular solid. (It is really more
like a trapezoidal solid.)
The maximum height (H) of the tub should probably be about 14 inches --
no higher in order to allow a person to step over. The minimum length
(L) of the tub should probably be the person's height (PH) plus a fudge
factor (6 inches or more; much more if you want a two-person tub ;-).
The minimum width (W) should probably be, say, 1.5 to 2 times the width
of the person at the hips (PW).

The minimum amount of water should be enough to cover the person's legs
-- typically more. So you need an estimate of the width of the legs
(PL) and the width of the hips.

Thus, the maximum volume of water (cu ft) is, assuming dimensions are
in inches (in A3):

=L * W * H / 1728

where L=PH+6 and W=1.5*PW at least, subject to real-world constraints.

The minimum volume of water is (in A4):

=L * W * PL / 1728

In some cell, you might write:

=if(A2 + A4 A3 - 4*L*W, "overflow!", "okay")

where 4*L*W represents a maximum water height with 4 inches of the top
of the top.

Caveat: This is just a quick off-hand estimate of things. I have not
bothered to check it for correctness. But hopefully it points you in
the right direction.

Thank you all so much for your insight. I was talking to my accounting
guy here who is uber smart with numbers obviously, and he brings in a
calculus book this morning to help me out!!! Yeah, I'm over my head.
But, very cool stuff nonetheless. I have much to learn.