Complex formula

[Marc]

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 (e-mail address removed)

 

[Jezebel]

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

 

[Biff]

Hi!

Try this:

A1 = weight

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

Biff

 

[Dana DeLouis]

Another option:

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

HTH. :>)

 

[Ron Rosenfeld]

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 (e-mail address removed)

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

 

[Marc]

Thanks for your help !

Marc

Hi!

Try this:

A1 = weight

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

Biff

 

[Marc]

Thanks for your help !
Marc

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]

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

 

[Marc]

Thanks for your help Ron !

Marc

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