help with complex formula that changes dependent on output

[Aaron_Tram]

Hello all. It has been a while since I posted here, but I've run into a scenario that I think should be easier than it is but I'm not sure how.

Some of the folks I work with developed a spreadsheet for dosing of a medication that we use frequently. The spreadsheet is pretty crude and could be improved GREATLY.

A little background. We use a medication which is infused IV constantly. The pump rate (in mL/24h) must be set at a value between 47 and 93 (for safety and accuracy reasons). The dose of the medication is dosed by weight and is adjusted over time. The total dose is therefore dependent on the patients weight (kg), the pump rate (mg/24h), and the concentration (ng/mL) of medication used.

We choose either the desired rate and need to calculate the dose, or vice versa. The spreadsheet is to generate a table of values for adjustment of dose.

For calculating dose: dose = (pump rate * concentration) / (weight * 24 * 60)
For calculating pump rate: rate = total dose * weight * 24 * 60 / concentration
See the attached spreadsheet which uses these formulas.

The part that gets tricky is that the available concentrations are: 5,000 (ng/mL), 10,000, 15,000, 30,000, 45,000 (etc, up by 15,000 increments).
So, if you look at the spreadsheet, sheet #1, the formula calculates the CADD Pump Rate and we adjust manually column C. My solution was to add conditional formatting to make things easier to see, but this is not a very elegant solution. It would be nice to have a formula that said: if D15 is >93, then adjust C15 up to the next concentration. Would this potentially involve a lookup table? I just don't know.

The 2nd worksheet I think is a more tricky one b/c we are varying the rate by a set interval to calculate the resultant total dose, but want the rate to stay between the allocated 47-93 range. I made this sheet manually and that's the way we have done it.

Any help would be greatly appreciated. Especially on the 1st sheet. I don't know about the 2nd one as it seems more complicated.

Thank you all! Happy to answer questions if the above explanation doesn't make sense.

EpoWorksheet.xlsx

[sourabhg98]

For your first part try the attached ...

[Aaron_Tram]

sourabhg98,
Your solution works great. I have noticed one issue though.
If you use the same sheet later for a 2nd calculation, then the range "C" has been replaced by numbers rather than formulae and the macro cannot decrease the dose.
I thought one solution would be to put the "starting place" in a user-entered range above the output range. There is an attached revised .xlsx for details. However, I still ran into the same problem. Next, I tried to make a hidden row (#13) that would persist as the starting point and use row 14 to reference 13. However, if row 14 is updated by the macro, there's no going back (except manually).
Any thoughts here? It is already improved drastically!
Thanks so much!

[Aaron_Tram]

Disregard my earlier reply. I've learned from and improved your macro to work better for me.

See my attached excel file. The problem I had run into was that the macro would replace the values in the top row of the "output" section and then the spreadsheet would need to be reset. So, I think I've helped with that although sometimes it takes a couple pushes of the "adjust" button. That is fine.

Now, on to the 2nd worksheet. Mostly we use the 1st worksheet b/c we want to adjust the dose by a fixed amount. However, there are times when we want to adjust the pump by fixed amounts and have the sheet tell us what the resultant doses are. The dose and rate are obviously dependent on one another.
Dose = (rate / (weight * 60 * 24))

I want to be able to put in the initial rate for the top row (B11 in my sheet) OR put in the initial dose for the top row (D11 in my sheet). I want to also input the interval for which the rate changes as you go down the sheet.
The same constraints need to be placed on the rate (no less than 47, no more than 93) and if outside that range, the concentration should be adjusted up or down in the increments allowed in the former sheet (5000,10000,15000,30000,45000 and up by 15000's).

Hope this makes sense. Thanks again for all the help.
AT

EpoWorksheet20150310_improved.xlsm

[sourabhg98]

I can try for the second part if you could explain what you need precisely..

[Aaron_Tram]

I can try for the second part if you could explain what you need precisely..

See my other reply. Thanks!

[bebo021999]

Fisrt start with sheet1, cell C11:

=INDEX({5,10,15,30,45,60,75}*1000,MATCH(1,(((B11*$B$6*24*60/({5,10,15,30,45,60,75}*1000))>=48)*((B11*$B$6*24*60/({5,10,15,30,45,60,75}*1000))<=93)),0))

For the first dose (2ng), there is no lower concentration then returns N/A.
Similar to last dose (78 ng), there is no higher concentration then returns N/A.
If there is lower or higher concentration, add it to the range {5,10,...} in ascending order.

If it works, keep going on sheet 2 later.

[sourabhg98]

Hi
I still have some doubts and want them to be cleared-
First you need to input the rates in column B...the first rate would be provided by you as you said and then it would incremented down the row according to the provided increment but as soon as it would exceed 93 we would increase the Concentration by the former increments given by you.
But then what would be the corresponding pump rate?
Like you have 90 in B19, now if we would increment it by 5 it would become more than 93 so we instead increased the Concentration to 10000 but how do we fix the rate in B20, B29, B36 etc...