help with formula
 

I am trying to set up a worksheet so I can work out how long medication
reduction will take. I need to input a number (the starting dose) and then
input a second number (reduction amount) to reduce the first number by. As
medication is taken twice daily the results need to be spread over two
columns for morning and afternoon.

So far I have days starting in column A8, morning dose in column B8 and
afternoon dose in column C8. I have the starting dose in C4 and reduction
amount in C6. I took an arbitrary value of 100 to start and used =C4/2 as
the first value in column B8 and =C4-B8 as the first value in column C8. I
then used =(C4/2)-C6 to get the next days figure (the starting dose less the
reduction amount). I just referenced previous cells to get remaining days
value. I realise there is probably a much more elegant way to do this
worksheet but I am only a beginner. So far my worksheet looks a bit like
this only it goes down around 110 columns.

Start Total 100
Daily Reduction 2

A B C
8 Day1 50 50
9 Day2 48 50
10 Day3 48 48
11 Day4 46 48

Daily Total 2600 2700

Total 5300

The problem I have is when I input a starting value less than 100 I now get
negative numbers and they are subtracted from the total amount. How do I get
a formula to ignore negative values or better still just return 0 for any
negative numbers. I have sub totalled both morning and afternoon columns and
then added both sub totals to get an over all amount. I thought an if
logical test on the sub totals would be workable but I don't know how to get
the formula to test multiple cells down a column, and/or how do I get each
days figure to ignore negative values. Any ideas?

Glen

Hi Glen

try the following two formulas:
in B10
=IF(B9=C9,IF(B9-$C$60,B9-$C$6,0),B9)
in C10
=IF(B9=C9,C9,B10)

and then fill them down the days.

Hope this helps
Cheers
JulieD


"Glen" wrote in message
...
I am trying to set up a worksheet so I can work out how long medication
reduction will take. I need to input a number (the starting dose) and then
input a second number (reduction amount) to reduce the first number by. As
medication is taken twice daily the results need to be spread over two
columns for morning and afternoon.

So far I have days starting in column A8, morning dose in column B8 and
afternoon dose in column C8. I have the starting dose in C4 and reduction
amount in C6. I took an arbitrary value of 100 to start and used =C4/2 as
the first value in column B8 and =C4-B8 as the first value in column C8. I
then used =(C4/2)-C6 to get the next days figure (the starting dose less
the
reduction amount). I just referenced previous cells to get remaining days
value. I realise there is probably a much more elegant way to do this
worksheet but I am only a beginner. So far my worksheet looks a bit like
this only it goes down around 110 columns.

Start Total 100
Daily Reduction 2

A B C
8 Day1 50 50
9 Day2 48 50
10 Day3 48 48
11 Day4 46 48

Daily Total 2600 2700

Total 5300

The problem I have is when I input a starting value less than 100 I now
get
negative numbers and they are subtracted from the total amount. How do I
get
a formula to ignore negative values or better still just return 0 for any
negative numbers. I have sub totalled both morning and afternoon columns
and
then added both sub totals to get an over all amount. I thought an if
logical test on the sub totals would be workable but I don't know how to
get
the formula to test multiple cells down a column, and/or how do I get each
days figure to ignore negative values. Any ideas?

Glen

Thank you very much JulieD. That worked fine. Can I just ask one more
question. What does the dollar sign do in the formula?

Glen



"JulieD" wrote in message
...
Hi Glen

try the following two formulas:
in B10
=IF(B9=C9,IF(B9-$C$60,B9-$C$6,0),B9)
in C10
=IF(B9=C9,C9,B10)

and then fill them down the days.

Hope this helps
Cheers
JulieD


"Glen" wrote in message
...
I am trying to set up a worksheet so I can work out how long medication
reduction will take. I need to input a number (the starting dose) and
then
input a second number (reduction amount) to reduce the first number by.
As
medication is taken twice daily the results need to be spread over two
columns for morning and afternoon.

So far I have days starting in column A8, morning dose in column B8 and
afternoon dose in column C8. I have the starting dose in C4 and reduction
amount in C6. I took an arbitrary value of 100 to start and used =C4/2 as
the first value in column B8 and =C4-B8 as the first value in column C8.
I
then used =(C4/2)-C6 to get the next days figure (the starting dose less
the
reduction amount). I just referenced previous cells to get remaining days
value. I realise there is probably a much more elegant way to do this
worksheet but I am only a beginner. So far my worksheet looks a bit like
this only it goes down around 110 columns.

Start Total 100
Daily Reduction 2

A B C
8 Day1 50 50
9 Day2 48 50
10 Day3 48 48
11 Day4 46 48

Daily Total 2600 2700

Total 5300

The problem I have is when I input a starting value less than 100 I now
get
negative numbers and they are subtracted from the total amount. How do I
get
a formula to ignore negative values or better still just return 0 for any
negative numbers. I have sub totalled both morning and afternoon columns
and
then added both sub totals to get an over all amount. I thought an if
logical test on the sub totals would be workable but I don't know how to
get
the formula to test multiple cells down a column, and/or how do I get
each
days figure to ignore negative values. Any ideas?

Glen

The $ character in cell references prevents Excel from adjusting
that part of the reference when you copy or fill a formula across
a range. So in Julie's formula, the $C$6 will not change but the
B9 and C9 will as you fill the formula down the range. See
http://www.cpearson.com/excel/relative.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Glen" wrote in message
...
Thank you very much JulieD. That worked fine. Can I just ask
one more question. What does the dollar sign do in the formula?

Glen



"JulieD" wrote in message
...
Hi Glen

try the following two formulas:
in B10
=IF(B9=C9,IF(B9-$C$60,B9-$C$6,0),B9)
in C10
=IF(B9=C9,C9,B10)

and then fill them down the days.

Hope this helps
Cheers
JulieD


"Glen" wrote in message
...
I am trying to set up a worksheet so I can work out how long
medication
reduction will take. I need to input a number (the starting
dose) and then
input a second number (reduction amount) to reduce the first
number by. As
medication is taken twice daily the results need to be spread
over two
columns for morning and afternoon.

So far I have days starting in column A8, morning dose in
column B8 and
afternoon dose in column C8. I have the starting dose in C4
and reduction
amount in C6. I took an arbitrary value of 100 to start and
used =C4/2 as
the first value in column B8 and =C4-B8 as the first value in
column C8. I
then used =(C4/2)-C6 to get the next days figure (the
starting dose less the
reduction amount). I just referenced previous cells to get
remaining days
value. I realise there is probably a much more elegant way to
do this
worksheet but I am only a beginner. So far my worksheet looks
a bit like
this only it goes down around 110 columns.

Start Total 100
Daily Reduction 2

A B C
8 Day1 50 50
9 Day2 48 50
10 Day3 48 48
11 Day4 46 48

Daily Total 2600 2700

Total 5300

The problem I have is when I input a starting value less than
100 I now get
negative numbers and they are subtracted from the total
amount. How do I get
a formula to ignore negative values or better still just
return 0 for any
negative numbers. I have sub totalled both morning and
afternoon columns and
then added both sub totals to get an over all amount. I
thought an if
logical test on the sub totals would be workable but I don't
know how to get
the formula to test multiple cells down a column, and/or how
do I get each
days figure to ignore negative values. Any ideas?

Glen

Many thanks.

Glen


"Chip Pearson" wrote in message
...
The $ character in cell references prevents Excel from adjusting that part
of the reference when you copy or fill a formula across a range. So in
Julie's formula, the $C$6 will not change but the B9 and C9 will as you
fill the formula down the range. See
http://www.cpearson.com/excel/relative.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Glen" wrote in message
...
Thank you very much JulieD. That worked fine. Can I just ask one more
question. What does the dollar sign do in the formula?

Glen



"JulieD" wrote in message
...
Hi Glen

try the following two formulas:
in B10
=IF(B9=C9,IF(B9-$C$60,B9-$C$6,0),B9)
in C10
=IF(B9=C9,C9,B10)

and then fill them down the days.

Hope this helps
Cheers
JulieD


"Glen" wrote in message
...
I am trying to set up a worksheet so I can work out how long medication
reduction will take. I need to input a number (the starting dose) and
then
input a second number (reduction amount) to reduce the first number by.
As
medication is taken twice daily the results need to be spread over two
columns for morning and afternoon.

So far I have days starting in column A8, morning dose in column B8 and
afternoon dose in column C8. I have the starting dose in C4 and
reduction
amount in C6. I took an arbitrary value of 100 to start and used =C4/2
as
the first value in column B8 and =C4-B8 as the first value in column
C8. I
then used =(C4/2)-C6 to get the next days figure (the starting dose
less the
reduction amount). I just referenced previous cells to get remaining
days
value. I realise there is probably a much more elegant way to do this
worksheet but I am only a beginner. So far my worksheet looks a bit
like
this only it goes down around 110 columns.

Start Total 100
Daily Reduction 2

A B C
8 Day1 50 50
9 Day2 48 50
10 Day3 48 48
11 Day4 46 48

Daily Total 2600 2700

Total 5300

The problem I have is when I input a starting value less than 100 I now
get
negative numbers and they are subtracted from the total amount. How do
I get
a formula to ignore negative values or better still just return 0 for
any
negative numbers. I have sub totalled both morning and afternoon
columns and
then added both sub totals to get an over all amount. I thought an if
logical test on the sub totals would be workable but I don't know how
to get
the formula to test multiple cells down a column, and/or how do I get
each
days figure to ignore negative values. Any ideas?

Glen