Writing a simple macro or formula

[Robin]

Hi I don't know how to do this but I know it can be done. I want to write a
simple macro (or maybe just a formula) - it doesn't have to look that pretty
as I am the only one using it. It is the following:

STEPS TO CALCULATE USUAL MAINTENANCE FLUIDS:
1. Convert child's wt in lbs to kg
2. Calculate 100 ml of fluid/kg/24 hr for the 1st 10 kg of wt
3. Calculate 50 ml of fluid/kg/24 hr for the 2nd 10 kg of wt
4. Calculate 10 to 25 ml of fluid/kg/24 hr for each kg over 20 of wt
5. Add the products of steps 2,3, and 4 to determine the ml of fluid
needed/24 hrs


So I want to be able to plug in a weight in pounds, and this "macro" give me
the individual answers for each step, then the total at the end. And yes I
can do it on paper all day long I just want to be able to do it quicker and
check myself.

Thanks
Robin

 

[M C Del Papa]

Robin,

You definitely don't need a macro to do this. A simple formula using some
IF...THEN statements will do fine. Below is one approach (note I skipped
some rows and columns so you can add headings, etc.):

Enter wt in cell B3
Convert to kg in B4: =B3*0.453593

Enter desired fluid rate for 1st 10 kg in B7
Calculate fluid rate in C7: =IF(B3>=10,10*B7,B3*B7)
Enter desired fluid rate for 2nd 10 kg in B8
Calculate fluid rate in C8: =IF(B3>=20,10*B8,IF(B3>=10,(B3-10)*B8,0))
Enter desired fluid rate for >20 kg in B9
Calculate fluid rate in C9: =IF(B3>=20,(B3-20)*B9,0)

Sum in cell C10 to get the total fluid rate: =SUM(C7:C9)

MC

 

[MC]

Oops! Make sure to change all the B3 references in the IF...THEN statements
to reference B4.

MC

 

[Robin]

Your formula is close - I have figured it out with pencil and paper and it
is very close but I am just trying to figure out a volume not a rate - so the
only value I have to input is the 15 pounds. From that all of the numbers
should fall out. So on paper it looks like this.

This is the problem:

1. Convert child's wt in lbs to kg
2. Calculate 100 ml of fluid/kg/24 hr for the 1st 10 kg of wt
3. Calculate 50 ml of fluid/kg/24 hr for the 2nd 10 kg of wt
4. Calculate 10 to 25 ml of fluid/kg/24 hr for each kg over 20 of wt
5. Add the products of steps 2,3, and 4 to determine the ml of fluid
needed/24 hrs

This is the solution on paper:

Maintenance Fluids
15lb – 28.4ml/hr for a total of 681.6ml/24 hours
40lb – 58.6ml/hr for a total of 1,406.4ml/24 hours
70lb – 72.3ml/hr for a total of 1,735.2ml/24 hours

So again the only input is the weight in pounds.

You are so kind to help me and I hope this will not offend you. Thanks for
taking another look at it. I am using your suggestions and trying to build it
from there but I think it is hard to understand without a medical background.
If the child ends up not weighing over 10kg then the amount of fluid is just
100ml/kg, but if the child weighs more than 20kg then I have to figure 100ml
for the 10kg + 50ml for the next 10kg + 20ml for each additional kg.

I think we are so on the right track - just a little refining. I used to
write stuff in Excel all the time but that was a long time ago and I just
don't remember it anymore. Again THANKS SO MUCH for looking at this.

 

[MC]

Robin,

I checked my answers against the ones we gave for 15, 40, and 70 lbs and got
basically the same answers with small rounding errors due to Excel's
12-digit precision versus hand calculations. Perhaps my earlier response was
confusing. You only have to enter numbers one time in Excel and from there
forward if you want to only change the weight, the spreadsheet calculations
will flow all the way through. There is no need to re-enter numbers
previously entered if they do not change. This is the beauty of Excel.
However, if you prefer a single formula that simply takes the child's weight
and calculates the fluid then it would be the following:

Enter the weight in pounds in cell B3
Copy the following formula to any cell:
=(IF(B3*0.453593>=10,10*100/24,B3*0.453593*100/24)+IF(B3*0.453593>=20,10*50/24,IF(B3*0.453593>=10,(B3*0.453593-10)*50/24,0))+IF(B3*0.453593>=20,(B3*0.453593-20)*20/24,0))*24

As you can see it is pretty long, but get's the job done. I initially
structured my response to allow the flexibility of changing the fluid rates
because you sounded like the amount of fluid above 20 kg could range between
10 to 25 ml/kg/24 hr. The disadvantage of the formula above is 20 ml/kg/24
hr is hardcoded into the formula. Therefore if you wanted to change this
value you would have to edit the formula in Excel which is far from an
elegant approach subject to mistakes and I would not recommend it.

Hope this helps!

MC

 

[Dana DeLouis]

Hi. If I am not mistaken, I think the values above 20 are double-added. I
get slightly different answers.
I'll take a crack at it, but I may be wrong...
For this example, enter a weight in pounds in A1. In another cell with a
range name "kg", enter...

=CONVERT(A1,"lbm","kg")

The Op wasn't clear on what value he was using for over 20, but I'll call
that value "Q".
If we set "Q" equal to about 20, we get values that are close to his
example...
Here is your excellent equation slightly modified:

=MIN((25*Kg)/6,(25*(Kg+10))/12,(Q*(Kg-20)+1500)/24)

I like to use Range Names when the equation gets a little out of hand...
Hope I got this right...