Tech Support Guy banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
137 Posts
Discussion Starter · #1 ·
Hi,

Don't even know what formula I am after here. I've played around with IF and AVERAGE, but can't get this to work.

What I'm trying to do is create a spreadsheet that will keep track of a pill dosage. The dosage has to increase every 5 days, by the same amount, 20mg's. I want to be able to see what dosage a person would be on at any given day in the period.

I have attached a sample where I have manually done it, but is there a formula that can do this with a little more elegance? (Ignore the first two increases, which take place every 3 days, the default increase is every 5 days)

Thanks in advance!

Bleep
 

Attachments

·
Super Moderator
Joined
·
79,318 Posts
make D11 = D7 + 5, then just copy and paste those 5 cells down. If that's all you need to do, that will do the trick.
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #3 ·
Hi valis,

Sorry, I don't get you. Won't that then add 5 to every day? I'm trying to increase it by 20, every 5 days. I would also like to be able to set both the 5 and the 20 increases as variables, so I can play around a bit and see what various dosages will end up costing.

Hope I am being clear!
 

·
Super Moderator
Joined
·
79,318 Posts
sorry, my error. Thought you meant increase by five.

If you want to set it up as variables, you will most likely need a macro, and I'll defer that to some of the vba coders around here. If you just want it to be static for x amount of time, just make the 5 cell down equal to the starting cell + 20, and then copy the 5 cells, paste them below the 5th cell, and repeat for the duration.

But let's wait until one of the vba gang get here, and they'll pony up a macro for you.
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #6 ·
I was hoping a formula could sort this out for me - if it turns out I have to resort to vba, I'll just do it manually - Its a one-off thing, so no biggie. Thought I would learn about some cool formula I hadn't previously used...

Thanks.

Bleep
 

·
Super Moderator
Joined
·
79,318 Posts
pretty sure that there isn't a formula for it, but stick around, you never know what others may come up with.
 

·
Registered
Joined
·
8,546 Posts
"The dosage has to increase every 5 days, by the same amount, 20mg's."

Those two are constants, then.

"I want to be able to see what dosage a person would be on at any given day in the period."

Day 19, for example? Then enter "19" in A9, and:

=INT(A9/B4)

in A10 to give "whole 5 day periods elapsed" (returns 3), and:

=MIN(D:D)+(B5*A10)

in A11 to give dosage on day 19 (returns 75, = 15 + (20*3)).

HTH :)
 

·
Super Moderator
Joined
·
79,318 Posts
thanks, bomb......owe you one.
 
1 - 10 of 10 Posts
Status
Not open for further replies.
Top