|
|
|
|
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
|