Writing a simple macro or formula

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

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

"Robin" <[email protected]> wrote in message
news:[email protected]...
> 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

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

MC

"M C Del Papa" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "Robin" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

"Robin" <[email protected]> wrote in message news:[email protected]...
> 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

Supposing you put the weight in pounds in column A
and supposing 1 pound is 0.45359 kg,
in cell B1:
= 0.45359 * a1
in cell C1:
= IF( B1>=10, 100, 0 )
in cell D1:
= IF( B1>=20, 50, 0 )
in cell E1:
= IF( B1>=20, 10*(B1-20), 0 )
in cell F1:
= C1 + D1 + E1

Or, if you don't want the intermediate columns:
in cell C1:
= IF( B1>=10, 100, 0 ) + IF( B1>=20, 50, 0 ) + IF( B1>=20, 10*(B1-20), 0 )

But I'm not sure what you mean with "Calculate 10 to 25 ml",
so I have taken 10.

hth
Dirk Vdm

"Dirk Van de moortel" <[email protected]> wrote in message
news:%[email protected]...

[snip]

>
> Or, if you don't want the intermediate columns:
> in cell C1:
> = IF( B1>=10, 100, 0 ) + IF( B1>=20, 50, 0 ) + IF( B1>=20, 10*(B1-20), 0 )

which can be shortened to
= IF( B1>=10, 100, 0 ) + IF( B1>=20, 50+10*(B1-20), 0 )

Dirk Vdm

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" wrote:

> Oops! Make sure to change all the B3 references in the IF...THEN statements
> to reference B4.
>
> MC
>
> "M C Del Papa" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> > "Robin" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >
> >
>
>
>

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

"Robin" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> Oops! Make sure to change all the B3 references in the IF...THEN
>> statements
>> to reference B4.
>>
>> MC
>>
>> "M C Del Papa" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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
>> >
>> > "Robin" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >
>> >
>>
>>
>>

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...
--
HTH. :>)

Dana DeLouis
Windows XP, Office 2003


"MC" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Robin" <[email protected]> wrote in message
> news:[email protected]...
>> 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" wrote:
>>
>>> Oops! Make sure to change all the B3 references in the IF...THEN
>>> statements
>>> to reference B4.
>>>
>>> MC
>>>
>>> "M C Del Papa" <[email protected]> wrote in message
>>> news:%[email protected]...
>>> > 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
>>> >
>>> > "Robin" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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
>>> >
>>> >
>>>
>>>
>>>
>
>