Nested IF functions

Originally Posted by NBVC

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.

I have embedded the spreadsheet in a word doc as the spreadsheet doesn't seem to attach.
Colum is the formula I have inserted. From day 33 I need the initial dose to stop being added, then the second 400mg (12.5mg) to stop being added from days 35 etc

Thanks
Ryan

Originally Posted by NBVC

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

I've coloured the column in yellow which the expected values are. Above the yellow the if formula is calculating the values correctly, but after the yellow starts I need a formula that will reduce the value by 12.5 every second day (to correspond with the end of the life of that dose in the blood).

After day 32 there will be no more values in column c and d as there will be no more dosing with the drug. The drug will then exit the blood stream based on the life, 32 days, so each initial dose will no longer release 12.5 into the blood stream after 32 days from that dose.

Originally Posted by deadlyduck

Try this in cell E2:

Please Login or Register  to view this content.

Copy down as appropriate

Thanks-appreciate the feedback.

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.

Thanks

Ryan

Originally Posted by tigertiger

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

This seems to work, even if I change values, but I would appreciate an explanation on the MOD function as I am unfamliliar with this.

Appreciate the help

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

Originally Posted by NBVC

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

Please Login or Register  to view this content.

after inserting a blank row above the table.

Thanks-will try this as well.

Appreciate the help all-looking at this I guess I have a lot more excel reading to do

why don't you use the short formula:

=E2+IF(B3<=32,(C3="x")*D3/32,-12.5*(MOD(B3,2)=1))

with column B contains number of days (the same in your file) -
instead of depending on row() ?

Originally Posted by deadlyduck

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.

Appreciate it-thanks

Originally Posted by tigertiger

why don't you use the short formula:

=E2+IF(B3<=32,(C3="x")*D3/32,-12.5*(MOD(B3,2)=1))

with column B contains number of days (the same in your file) -
instead of depending on row() ?

Formula works great-thanks. Only thing is it relies on the "x"value I put in, but if the dose is taken on another day, would have to alter the x value to a different day.
But it is working well-appreciate the help

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:

Please Login or Register  to view this content.

and copy down...

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

Thanks for the replies all. They all work, now I just have to figure out how

Appreciate the help