Solved: Excel help - don't know what formula applies

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bleep69

Thread Starter
Joined
Nov 21, 2008
Messages
137
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

valis

Moderator
Joined
Sep 24, 2004
Messages
78,021
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.
 

bleep69

Thread Starter
Joined
Nov 21, 2008
Messages
137
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!
 

valis

Moderator
Joined
Sep 24, 2004
Messages
78,021
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.
 

bleep69

Thread Starter
Joined
Nov 21, 2008
Messages
137
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
 

valis

Moderator
Joined
Sep 24, 2004
Messages
78,021
pretty sure that there isn't a formula for it, but stick around, you never know what others may come up with.
 
Joined
Jul 1, 2005
Messages
8,546
"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 :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top