Thread: formula help

sorry if this is too simple.
I would like to write a formula which will calculate a dose range of
medication based on patient's weight. I would like the data displayed such
that it would read (0.2-0.3 mg). This formula needs to have upper an lower
limits on the dose. THe dose cannont be lower than 0.2 nor higher than 3,
but all values in between should show.

here is what I have so far. I am unable for figure out how to impose the
upper limit. D3 location of user defined patient weight.


=CONCATENATE(IF(PRODUCT(0.067,D3)<=0.2,VALUE(0.2),IF(PRODUCT(0.067,D3)>0.2,VALUE(PRODUCT(0.067,D3))& "-"&CONCATENATE(IF(PRODUCT(0.1,D3)<=0.3,VALUE(0.3),IF(PRODUCT(0.2,D3)>0.3,VALUE(PRODUCT(0.2,D3)))) ))))

Possibly:

=if(D3*.067<=.2,"0.2 mg",if(D3*.067>.3,"0.3 mg","0.2-0.3 mg"))

--
Regards,
Tom Ogilvy


"kenneth" <kenneth@discussions.microsoft.com> wrote in message
news:283A0BF2-5065-4377-B0DC-ED9F2FEB40C6@microsoft.com...
> sorry if this is too simple.
> I would like to write a formula which will calculate a dose range of
> medication based on patient's weight. I would like the data displayed
such
> that it would read (0.2-0.3 mg). This formula needs to have upper an
lower
> limits on the dose. THe dose cannont be lower than 0.2 nor higher than 3,
> but all values in between should show.
>
> here is what I have so far. I am unable for figure out how to impose the
> upper limit. D3 location of user defined patient weight.
>
>
>
=CONCATENATE(IF(PRODUCT(0.067,D3)<=0.2,VALUE(0.2),IF(PRODUCT(0.067,D3)>0.2,V
ALUE(PRODUCT(0.067,D3))&"-"&CONCATENATE(IF(PRODUCT(0.1,D3)<=0.3,VALUE(0.3),I
F(PRODUCT(0.2,D3)>0.3,VALUE(PRODUCT(0.2,D3))))))))
>
>

Thanks for the suggestion. The formula will not provide the true value for
results that are > 0.2 but less than 3. I would like to have the result show
as a range of true values between 0.2 and 3 but if the values are < 0.2 then
0.2-0.3 should be the out put and if the values are > 3, then 2-3 should be
the output.

any insight?


"Tom Ogilvy" wrote:

> Possibly:
>
> =if(D3*.067<=.2,"0.2 mg",if(D3*.067>.3,"0.3 mg","0.2-0.3 mg"))
>
> --
> Regards,
> Tom Ogilvy
>
>
> "kenneth" <kenneth@discussions.microsoft.com> wrote in message
> news:283A0BF2-5065-4377-B0DC-ED9F2FEB40C6@microsoft.com...
> > sorry if this is too simple.
> > I would like to write a formula which will calculate a dose range of
> > medication based on patient's weight. I would like the data displayed
> such
> > that it would read (0.2-0.3 mg). This formula needs to have upper an
> lower
> > limits on the dose. THe dose cannont be lower than 0.2 nor higher than 3,
> > but all values in between should show.
> >
> > here is what I have so far. I am unable for figure out how to impose the
> > upper limit. D3 location of user defined patient weight.
> >
> >
> >
> =CONCATENATE(IF(PRODUCT(0.067,D3)<=0.2,VALUE(0.2),IF(PRODUCT(0.067,D3)>0.2,V
> ALUE(PRODUCT(0.067,D3))&"-"&CONCATENATE(IF(PRODUCT(0.1,D3)<=0.3,VALUE(0.3),I
> F(PRODUCT(0.2,D3)>0.3,VALUE(PRODUCT(0.2,D3))))))))
> >
> >
>
>
>

Hi Kenneth

If I understand you correctly then
=IF(D3*0.067<0.2,"0.2 - 0.3 mg",IF(D3*0.067>3," 2.0 - 3.0 mg",
TEXT(D3*0.067,"0.0")&" - "&TEXT(D3*0.1,"0.0")&" mg"))
will provide your requirements.

--
Regards

Roger Govier


"kenneth" <kenneth@discussions.microsoft.com> wrote in message
news:8A484F16-6CA9-4241-A096-A422DF25F3C5@microsoft.com...
> Thanks for the suggestion. The formula will not provide the true
> value for
> results that are > 0.2 but less than 3. I would like to have the
> result show
> as a range of true values between 0.2 and 3 but if the values are <
> 0.2 then
> 0.2-0.3 should be the out put and if the values are > 3, then 2-3
> should be
> the output.
>
> any insight?
>
>
> "Tom Ogilvy" wrote:
>
>> Possibly:
>>
>> =if(D3*.067<=.2,"0.2 mg",if(D3*.067>.3,"0.3 mg","0.2-0.3 mg"))
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "kenneth" <kenneth@discussions.microsoft.com> wrote in message
>> news:283A0BF2-5065-4377-B0DC-ED9F2FEB40C6@microsoft.com...
>> > sorry if this is too simple.
>> > I would like to write a formula which will calculate a dose range
>> > of
>> > medication based on patient's weight. I would like the data
>> > displayed
>> such
>> > that it would read (0.2-0.3 mg). This formula needs to have upper
>> > an
>> lower
>> > limits on the dose. THe dose cannont be lower than 0.2 nor higher
>> > than 3,
>> > but all values in between should show.
>> >
>> > here is what I have so far. I am unable for figure out how to
>> > impose the
>> > upper limit. D3 location of user defined patient weight.
>> >
>> >
>> >
>> =CONCATENATE(IF(PRODUCT(0.067,D3)<=0.2,VALUE(0.2),IF(PRODUCT(0.067,D3)>0.2,V
>> ALUE(PRODUCT(0.067,D3))&"-"&CONCATENATE(IF(PRODUCT(0.1,D3)<=0.3,VALUE(0.3),I
>> F(PRODUCT(0.2,D3)>0.3,VALUE(PRODUCT(0.2,D3))))))))
>> >
>> >
>>
>>
>>

[QUOTE=kenneth]Thanks for the suggestion. The formula will not provide the true value for
results that are > 0.2 but less than 3. I would like to have the result show
as a range of true values between 0.2 and 3 but if the values are < 0.2 then
0.2-0.3 should be the out put and if the values are > 3, then 2-3 should be
the output.

any insight?

try
IF(0.67*D3<=0.2,"0.2-0.3 mg", IF(0.67*D3>3, "2 - 3 mg", 0.67*D3 & " mg"))

A V Veerkar

Brilliant!
Sorry for the post in 2 places.

"Roger Govier" wrote:

> Hi Kenneth
>
> If I understand you correctly then
> =IF(D3*0.067<0.2,"0.2 - 0.3 mg",IF(D3*0.067>3," 2.0 - 3.0 mg",
> TEXT(D3*0.067,"0.0")&" - "&TEXT(D3*0.1,"0.0")&" mg"))
> will provide your requirements.
>
> --
> Regards
>
> Roger Govier
>
>
> "kenneth" <kenneth@discussions.microsoft.com> wrote in message
> news:8A484F16-6CA9-4241-A096-A422DF25F3C5@microsoft.com...
> > Thanks for the suggestion. The formula will not provide the true
> > value for
> > results that are > 0.2 but less than 3. I would like to have the
> > result show
> > as a range of true values between 0.2 and 3 but if the values are <
> > 0.2 then
> > 0.2-0.3 should be the out put and if the values are > 3, then 2-3
> > should be
> > the output.
> >
> > any insight?
> >
> >
> > "Tom Ogilvy" wrote:
> >
> >> Possibly:
> >>
> >> =if(D3*.067<=.2,"0.2 mg",if(D3*.067>.3,"0.3 mg","0.2-0.3 mg"))
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "kenneth" <kenneth@discussions.microsoft.com> wrote in message
> >> news:283A0BF2-5065-4377-B0DC-ED9F2FEB40C6@microsoft.com...
> >> > sorry if this is too simple.
> >> > I would like to write a formula which will calculate a dose range
> >> > of
> >> > medication based on patient's weight. I would like the data
> >> > displayed
> >> such
> >> > that it would read (0.2-0.3 mg). This formula needs to have upper
> >> > an
> >> lower
> >> > limits on the dose. THe dose cannont be lower than 0.2 nor higher
> >> > than 3,
> >> > but all values in between should show.
> >> >
> >> > here is what I have so far. I am unable for figure out how to
> >> > impose the
> >> > upper limit. D3 location of user defined patient weight.
> >> >
> >> >
> >> >
> >> =CONCATENATE(IF(PRODUCT(0.067,D3)<=0.2,VALUE(0.2),IF(PRODUCT(0.067,D3)>0.2,V
> >> ALUE(PRODUCT(0.067,D3))&"-"&CONCATENATE(IF(PRODUCT(0.1,D3)<=0.3,VALUE(0.3),I
> >> F(PRODUCT(0.2,D3)>0.3,VALUE(PRODUCT(0.2,D3))))))))
> >> >
> >> >
> >>
> >>
> >>
>
>
>

[QUOTE=kenneth]Brilliant!
Sorry for the post in 2 places.

"Roger Govier" wrote:
[color=blue]
> Hi Kenneth
>
> If I understand you correctly then
> =IF(D3*0.067<0.2,"0.2 - 0.3 mg",IF(D3*0.067>3," 2.0 - 3.0 mg",
> TEXT(D3*0.067,"0.0")&" - "&TEXT(D3*0.1,"0.0")&" mg"))
> will provide your requirements.
>
> --
> Regards
>
> Roger Govier
>
>

Roger,

I think there is an error. For example if D3 is say 42 your formula would give "2.8 - 4.2 mg " which is not what Kenneth wants. I think he wants actual value upto 3 and "2 - 3 mg" if it exceeds 3.

There is an error in my formula as well. Instead of 0.67 it should read .067 everywhere. It should be

IF(0.067*D3<=0.2, "0.2-0.3 mg", IF(0.067*D3>3, "2.0-3.0 mg",TEXT(D3*0.067,"0.0")&" mg"))



A V Veerkar

Hi

Thank for pointing out the flaw.
Yes, you are correct, but at the same time your formula is not dealing
with the range that Kenneth posted, which was that the dose could be
between 0.067 x body weight and 0.1 x bodyweight.

My formula should be adjusted to
=IF(D3*0.067<0.2,"0.2 - 0.3 mg",IF(OR(D3*0.067>3,D3*0.1>3)," 2.0 - 3.0
mg",
TEXT(D3*0.067,"0.0")&" - "&TEXT(D3*0.1,"0.0")&" mg"))

which would return 2.0 - 3.0 mg for a 42 kilo bodyweight
or maybe

=IF(D3*0.067<0.2,"0.2 - 0.3 mg",IF(D3*0.067>3," 2.0 - 3.0 mg",
TEXT(D3*0.067,"0.0")&" - "&TEXT(MIN(3,D3*0.1),"0.0")&" mg"))

which would return 2.9 - 3.0 mg

The former might lead to a recommendation of too low a dose for that
weight if the minimum were adopted, whilst clearly the flaw in my
original solution, which you so rightly pointed out, could lead to a
dose rate exceeding the maximum recommendation.

If a range is not required for the intermediates, then of course you
solution provides the exact dose rate.

Only Kenneth can decide which of the alternatives he requires.

--
Regards

Roger Govier


"avveerkar" <avveerkar.22wxoo_1139406077.2127@excelforum-nospam.com>
wrote in message
news:avveerkar.22wxoo_1139406077.2127@excelforum-nospam.com...[color=blue]
>
> kenneth Wrote:
>> Brilliant!
>> Sorry for the post in 2 places.
>>
>> "Roger Govier" wrote:
>>
>> > Hi Kenneth
>> >
>> > If I understand you correctly then
>> > =IF(D3*0.067<0.2,"0.2 - 0.3 mg",IF(D3*0.067>3," 2.0 - 3.0 mg",
>> > TEXT(D3*0.067,"0.0")&" - "&TEXT(D3*0.1,"0.0")&" mg"))
>> > will provide your requirements.
>> >
>> > --
>> > Regards
>> >
>> > Roger Govier
>> >
>> >
>>
>> Roger,
>>
>> I think there is an error. For example if D3 is say 42 your formula
>> would give "2.8 - 4.2 mg " which is not what Kenneth wants. I think
>> he
>> wants actual value upto 3 and "2 - 3 mg" if it exceeds 3.
>>
>> There is an error in my formula as well. Instead of 0.67 it should
>> read
>> .067 everywhere. It should be
>>
>> IF(0.067*D3<=0.2, "0.2-0.3 mg", IF(0.067*D3>3, "2.0-3.0
>> mg",TEXT(D3*0.067,"0.0")&" mg"))
>>
>>
>>
>> A V Veerkar
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile:
> http://www.excelforum.com/member.php...o&userid=30338
> View this thread:
> http://www.excelforum.com/showthread...hreadid=509499
>