Darryl said:
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 basedon 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.
