Nested IF functions

Hi all

New member here and am hoping someone can help me a nested if function, or a better function for my problem.

I have a drug that is taken with a 16 day half life, therefore total life of the drug is 32 days. If 400mg taken on day one, 12.5mg will be released daily for the next 32 days. Then 400mg taken on day 3 another 12.5mg will be released from that date and so on.

For this I have a used a simple if function of =if(C2="x",D2/32,0) with this data set.



The problem I have is that at the end of the 32 days, I need to trend the values down, so the 12.5mg of the 400mg dose from day 1 will be deducted from the concentration column after 32 days/count, and then the 12.5mg from the 400mg day 3 dose will be deducted from the concentration after 32 count from day 3 and so on.

Idea is for the blood concentrations of the different doses will be added together over the next 32 days, but after the 32 days will then stop being as the life of the drug is up.

I'm sure a count function of some sort needs to be used in a nested if formula, but can't come up with something that works.

Would appreciate any help

Thanks

Ryan

I think it would be better if you actually posted a sample worksheet so that we can see where there may be blank cells, etc... in your table...

Also, please provided samples of expected results.

So what are the actual expected results in E33 onwards?

And are there still going to be inputs in columns C and D?

Try this in cell E2:

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

Copy down as appropriate

an alternative - try the following formulas:
at E2:
=(C3="x")*D3/32

at E3:
=IF(B3<=32,(C3="x")*D3/32,-12.5*(MOD(B3,2)=1))+E2
copy down for E4:E...

If you can insert a row above your table, so that Row 1 is blank,

Then in E3:

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

copied down

I'm finding that if I change the value from 400 to 500 in column D, I no longer get the correct figures.
I'm not sure how the MOD function works or the negative ROW function.

Appreciate any explanation on this.

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.

I don't know how I copy/paste deadlyduck's formula in my post... but I meant to post this formula: ...ooops.

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

after inserting a blank row above the table.

I don't know how I copied/pasted deadlyduck's formula in my post...

... I was trying to figure out why an extra row was needed for my formula but as it was one of the ExcelForum gurus who suggested it I decided there had to be a reason but was stumped if I could figure it out!

Thanks for clearing that up!

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

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

1. 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
2. Therefore, I needed to start deducting the daily concentration of that first dose (and subsequent doses) from row 33 onwards.
3. Each subsequent dose expired in row 35,37,39 etc
4. So I used the MOD(row(),2) function to identify whether or not a dose expired
5. 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.

Originally Posted by RyanF

Only thing is it relies on the "x"value I put in, but if the dose is taken on another day

if you want not to rely on "x", the formula will be:
=E2+IF(B3<=32,(MOD(B3,2)=1)*D3/32,-12.5*(MOD(B3,2)=1))

OR
=E2+(MOD(B3,2)=1)*IF(B3<=32,D3/32,-12.5)
with the same number of days in the two stages (1st & 2nd stage)

in the formulas, if also do not rely on the value in cplumn B - you can use Row() replace B3 appropriately

G luck.