Complex formula

Hi !

My name is Marc and I am a medical resident in Canada. I use MS Excel 2003.
I would need your help in order to find or "set up a calculation strategy" in
Excel.
When a child has lost lots of fluids (e.g by vomiting), I need to give him
some intravenous fluids. I need to calculate this amount of fluids based on
the child's weight. In clinic, the formula is :

- For the first 10 kg, it is 100 mL/kg
- For every kg between 10 and 20 kg (included), it is 50 mL/kg
- For every kg above 20 kg, it is 20 mL/kg)

For example,

The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg * 20
mL/kg) = 1780 mL


Would you have any suggestion for a formula to calculate the IV fluids only
by entering the child's weight in Excel.

Thank you for your help !

Marc A. Allard, MD, MSc
Please, send your suggestions to [email protected]

The way to approach this (especially given the criticality) is to break it
into parts, each in its own cell, then sum the results; that way you can
*see* that the calculation is working.

Define a name for the input cell containing the weight: eg call it 'Weight'

Cell 1: = Min(Weight * 100, 1000)

Cell 2: = IF(Weight > 10, Min((Weight - 10) * 50, 500), 0)

Cell 3: = IF(Weight > 20, (Weight - 20) * 20, 0)

Dosage = Cell 1 + Cell 2 + Cell 3




"Marc" <[email protected]> wrote in message
news:[email protected]...
> Hi !
>
> My name is Marc and I am a medical resident in Canada. I use MS Excel
> 2003.
> I would need your help in order to find or "set up a calculation strategy"
> in
> Excel.
> When a child has lost lots of fluids (e.g by vomiting), I need to give him
> some intravenous fluids. I need to calculate this amount of fluids based
> on
> the child's weight. In clinic, the formula is :
>
> - For the first 10 kg, it is 100 mL/kg
> - For every kg between 10 and 20 kg (included), it is 50 mL/kg
> - For every kg above 20 kg, it is 20 mL/kg)
>
> For example,
>
> The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg *
> 20
> mL/kg) = 1780 mL
>
>
> Would you have any suggestion for a formula to calculate the IV fluids
> only
> by entering the child's weight in Excel.
>
> Thank you for your help !
>
> Marc A. Allard, MD, MSc
> Please, send your suggestions to [email protected]

Hi!

Try this:

A1 = weight

=SUMPRODUCT(--(A1>{0,10,20}),(A1-{0,10,20}),{100,-50,-30})

Biff

"Marc" <[email protected]> wrote in message
news:[email protected]...
> Hi !
>
> My name is Marc and I am a medical resident in Canada. I use MS Excel
> 2003.
> I would need your help in order to find or "set up a calculation strategy"
> in
> Excel.
> When a child has lost lots of fluids (e.g by vomiting), I need to give him
> some intravenous fluids. I need to calculate this amount of fluids based
> on
> the child's weight. In clinic, the formula is :
>
> - For the first 10 kg, it is 100 mL/kg
> - For every kg between 10 and 20 kg (included), it is 50 mL/kg
> - For every kg above 20 kg, it is 20 mL/kg)
>
> For example,
>
> The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg *
> 20
> mL/kg) = 1780 mL
>
>
> Would you have any suggestion for a formula to calculate the IV fluids
> only
> by entering the child's weight in Excel.
>
> Thank you for your help !
>
> Marc A. Allard, MD, MSc
> Please, send your suggestions to [email protected]

Another option:

=MIN(100*Wgt, 500+50*Wgt, 1100+20*Wgt)

HTH. :>)
--
Dana DeLouis
Win XP & Office 2003


"Marc" <[email protected]> wrote in message
news:[email protected]...
> Hi !
>
> My name is Marc and I am a medical resident in Canada. I use MS Excel
> 2003.
> I would need your help in order to find or "set up a calculation strategy"
> in
> Excel.
> When a child has lost lots of fluids (e.g by vomiting), I need to give him
> some intravenous fluids. I need to calculate this amount of fluids based
> on
> the child's weight. In clinic, the formula is :
>
> - For the first 10 kg, it is 100 mL/kg
> - For every kg between 10 and 20 kg (included), it is 50 mL/kg
> - For every kg above 20 kg, it is 20 mL/kg)
>
> For example,
>
> The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg *
> 20
> mL/kg) = 1780 mL
>
>
> Would you have any suggestion for a formula to calculate the IV fluids
> only
> by entering the child's weight in Excel.
>
> Thank you for your help !
>
> Marc A. Allard, MD, MSc
> Please, send your suggestions to [email protected]

On Sun, 16 Oct 2005 20:12:03 -0700, "Marc" <[email protected]>
wrote:

>Hi !
>
>My name is Marc and I am a medical resident in Canada. I use MS Excel 2003.
>I would need your help in order to find or "set up a calculation strategy" in
>Excel.
>When a child has lost lots of fluids (e.g by vomiting), I need to give him
>some intravenous fluids. I need to calculate this amount of fluids based on
>the child's weight. In clinic, the formula is :
>
>- For the first 10 kg, it is 100 mL/kg
>- For every kg between 10 and 20 kg (included), it is 50 mL/kg
>- For every kg above 20 kg, it is 20 mL/kg)
>
>For example,
>
>The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
>The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
>The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg * 20
>mL/kg) = 1780 mL
>
>
>Would you have any suggestion for a formula to calculate the IV fluids only
>by entering the child's weight in Excel.
>
>Thank you for your help !
>
>Marc A. Allard, MD, MSc
>Please, send your suggestions to [email protected]

This is a common type of problem. Although more often seen with tax tables
than rehydration calculations <smile>.

I would set up a table with three columns showing your "base fluid amt" and
incremental amount for each of the weight breaks:

Wt Base Incremental
0 0 100
10 1000 50
20 1500 20

Select the range and NAME it "tbl" (Insert/Name/Define

I would then use the following formula:

=VLOOKUP(A1,tbl,2)+VLOOKUP(A1,tbl,3)*(A1-VLOOKUP(A1,tbl,1))

with the child's weight in A1, this will give you the fluid requirement.


--ron

Thanks for your help !

Marc


"Biff" wrote:

> Hi!
>
> Try this:
>
> A1 = weight
>
> =SUMPRODUCT(--(A1>{0,10,20}),(A1-{0,10,20}),{100,-50,-30})
>
> Biff
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > Hi !
> >
> > My name is Marc and I am a medical resident in Canada. I use MS Excel
> > 2003.
> > I would need your help in order to find or "set up a calculation strategy"
> > in
> > Excel.
> > When a child has lost lots of fluids (e.g by vomiting), I need to give him
> > some intravenous fluids. I need to calculate this amount of fluids based
> > on
> > the child's weight. In clinic, the formula is :
> >
> > - For the first 10 kg, it is 100 mL/kg
> > - For every kg between 10 and 20 kg (included), it is 50 mL/kg
> > - For every kg above 20 kg, it is 20 mL/kg)
> >
> > For example,
> >
> > The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> > The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> > The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg *
> > 20
> > mL/kg) = 1780 mL
> >
> >
> > Would you have any suggestion for a formula to calculate the IV fluids
> > only
> > by entering the child's weight in Excel.
> >
> > Thank you for your help !
> >
> > Marc A. Allard, MD, MSc
> > Please, send your suggestions to [email protected]
>
>
>

Thanks for your help !
Marc

"Jezebel" wrote:

> The way to approach this (especially given the criticality) is to break it
> into parts, each in its own cell, then sum the results; that way you can
> *see* that the calculation is working.
>
> Define a name for the input cell containing the weight: eg call it 'Weight'
>
> Cell 1: = Min(Weight * 100, 1000)
>
> Cell 2: = IF(Weight > 10, Min((Weight - 10) * 50, 500), 0)
>
> Cell 3: = IF(Weight > 20, (Weight - 20) * 20, 0)
>
> Dosage = Cell 1 + Cell 2 + Cell 3
>
>
>
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > Hi !
> >
> > My name is Marc and I am a medical resident in Canada. I use MS Excel
> > 2003.
> > I would need your help in order to find or "set up a calculation strategy"
> > in
> > Excel.
> > When a child has lost lots of fluids (e.g by vomiting), I need to give him
> > some intravenous fluids. I need to calculate this amount of fluids based
> > on
> > the child's weight. In clinic, the formula is :
> >
> > - For the first 10 kg, it is 100 mL/kg
> > - For every kg between 10 and 20 kg (included), it is 50 mL/kg
> > - For every kg above 20 kg, it is 20 mL/kg)
> >
> > For example,
> >
> > The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> > The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> > The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg *
> > 20
> > mL/kg) = 1780 mL
> >
> >
> > Would you have any suggestion for a formula to calculate the IV fluids
> > only
> > by entering the child's weight in Excel.
> >
> > Thank you for your help !
> >
> > Marc A. Allard, MD, MSc
> > Please, send your suggestions to [email protected]
>
>
>

Thanks a lot for your help !

I tried hard to understand to logic behind this formula and it wasn't a
success. But it works, that is the important

Marc


"Dana DeLouis" wrote:

> Another option:
>
> =MIN(100*Wgt, 500+50*Wgt, 1100+20*Wgt)
>
> HTH. :>)
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > Hi !
> >
> > My name is Marc and I am a medical resident in Canada. I use MS Excel
> > 2003.
> > I would need your help in order to find or "set up a calculation strategy"
> > in
> > Excel.
> > When a child has lost lots of fluids (e.g by vomiting), I need to give him
> > some intravenous fluids. I need to calculate this amount of fluids based
> > on
> > the child's weight. In clinic, the formula is :
> >
> > - For the first 10 kg, it is 100 mL/kg
> > - For every kg between 10 and 20 kg (included), it is 50 mL/kg
> > - For every kg above 20 kg, it is 20 mL/kg)
> >
> > For example,
> >
> > The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> > The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> > The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg *
> > 20
> > mL/kg) = 1780 mL
> >
> >
> > Would you have any suggestion for a formula to calculate the IV fluids
> > only
> > by entering the child's weight in Excel.
> >
> > Thank you for your help !
> >
> > Marc A. Allard, MD, MSc
> > Please, send your suggestions to [email protected]
>
>
>

Thanks for your help Ron !

Marc

"Ron Rosenfeld" wrote:

> On Sun, 16 Oct 2005 20:12:03 -0700, "Marc" <[email protected]>
> wrote:
>
> >Hi !
> >
> >My name is Marc and I am a medical resident in Canada. I use MS Excel 2003.
> >I would need your help in order to find or "set up a calculation strategy" in
> >Excel.
> >When a child has lost lots of fluids (e.g by vomiting), I need to give him
> >some intravenous fluids. I need to calculate this amount of fluids based on
> >the child's weight. In clinic, the formula is :
> >
> >- For the first 10 kg, it is 100 mL/kg
> >- For every kg between 10 and 20 kg (included), it is 50 mL/kg
> >- For every kg above 20 kg, it is 20 mL/kg)
> >
> >For example,
> >
> >The child is 9 kg : 9 kg * 100 mL/kg = 900 mL
> >The child is 16 kg : (10 kg * 100 mL/kg) + (6 kg * 50 mL/kg) =1300 mL
> >The child is 34 kg : (10 kg * 100 mL/kg) + (10 kg * 50 mL/kg) + (14 kg * 20
> >mL/kg) = 1780 mL
> >
> >
> >Would you have any suggestion for a formula to calculate the IV fluids only
> >by entering the child's weight in Excel.
> >
> >Thank you for your help !
> >
> >Marc A. Allard, MD, MSc
> >Please, send your suggestions to [email protected]
>
> This is a common type of problem. Although more often seen with tax tables
> than rehydration calculations <smile>.
>
> I would set up a table with three columns showing your "base fluid amt" and
> incremental amount for each of the weight breaks:
>
> Wt Base Incremental
> 0 0 100
> 10 1000 50
> 20 1500 20
>
> Select the range and NAME it "tbl" (Insert/Name/Define
>
> I would then use the following formula:
>
> =VLOOKUP(A1,tbl,2)+VLOOKUP(A1,tbl,3)*(A1-VLOOKUP(A1,tbl,1))
>
> with the child's weight in A1, this will give you the fluid requirement.
>
>
> --ron
>