Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |