Nested IF functions

**RyanF** · 06-16-2009, 11:02 AM

Originally Posted by deadlyduck

If you need the formula to work for different drugs or half-life durations the formula would need to be modified to allow for the revised concentration (ie dose/full life). If required, I can do this but as you've got the other formula working it may not be needed.

The MOD(number, divisor) function calculates the remainder of 'number' when divided by 'divisor' eg MOD(10,4) would be 2.

The ROW() function tells Excel the number of the row you are currently on. It's a useful way to perform computations based on 'how far down your formula has gone'.

Hope this helps.

Please ensure that if you are happy with the answer that you mark your original thread as solved.

Thanks-will do.

=IF(ROWS($D$3:D7)<=32,SUM($D$3:D7)/32,IF(MOD(ROW(),2)=1,SUM($D$3:D7)/32-((ROW()-33)/2)*12.5,E8))

I'm not clear on what you are doing in the green part of the formula.

I would appreciate a formula I could use similar to this to with adjusted drug life's and amounts, so I don't have to use an "X" to annotate the drug use on a particular day.
I would like to try have two cells somewhere on the sheet that the drug life and dose can be inputed into that would change the calcs. or use the days.

Appreciate the help

**NBVC** · 06-16-2009, 11:17 AM

Originally Posted by RyanF

I would appreciate a formula I could use similar to this to with adjusted drug life's and amounts, so I don't have to use an "X" to annotate the drug use on a particular day.
I would like to try have two cells somewhere on the sheet that the drug life and dose can be inputed into that would change the calcs. or use the days.

Appreciate the help

Using my solution.. see attached...

You can enter a Half-Life in H2 and a Dosage in I2 and then amend formula in E2 to:

=IF(AND(C2="",MAX($E$2:$E2)=$D$3/2),IF(E2=E1,E2-$D$3/($H$2*2),E2),(SUM(IF(C3="x",D3/($H$2*2),0),E2)))

and copy down...

**RyanF** · 06-16-2009, 03:14 PM

Originally Posted by NBVC

Using my solution.. see attached...

You can enter a Half-Life in H2 and a Dosage in I2 and then amend formula in E2 to:

=IF(AND(C2="",MAX($E$2:$E2)=$D$3/2),IF(E2=E1,E2-$D$3/($H$2*2),E2),(SUM(IF(C3="x",D3/($H$2*2),0),E2)))

and copy down...

Thanks-much appreciated. Now just have to figure what the hell you did

**deadlyduck** · 06-16-2009, 06:05 PM

I've modified my original formula so that you can vary the dose and/or half-life.
The attached has the various formulae in coloured cells or cells with a red border.
You only need to enter the half-life data and dose- the other coloured cells should automatically compute without the 'X' marks

**deadlyduck** · 06-16-2009, 11:27 AM

In the green part of the formula, I was doing the following:

As your first dose was in row 2 and the full life was 32 days, I knew that the row in which the first dose would expire was going to be in row 33
Therefore, I needed to start deducting the daily concentration of that first dose (and subsequent doses) from row 33 onwards.
Each subsequent dose expired in row 35,37,39 etc
So I used the MOD(row(),2) function to identify whether or not a dose expired
I could then deduct the relevant quantities of doses from the total

that's the basic idea.

To facilitate variable strengths and concentrations, I need to use a more generalised formula- I am working on that at the moment but may be a while before I get it sorted. You might need to check back later.

**RyanF** · 06-16-2009, 03:07 PM

Originally Posted by deadlyduck

In the green part of the formula, I was doing the following:

As your first dose was in row 2 and the full life was 32 days, I knew that the row in which the first dose would expire was going to be in row 33
Therefore, I needed to start deducting the daily concentration of that first dose (and subsequent doses) from row 33 onwards.
Each subsequent dose expired in row 35,37,39 etc
So I used the MOD(row(),2) function to identify whether or not a dose expired
I could then deduct the relevant quantities of doses from the total

that's the basic idea.

To facilitate variable strengths and concentrations, I need to use a more generalised formula- I am working on that at the moment but may be a while before I get it sorted. You might need to check back later.

Appreciate it-thanks