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

Discussion in 'Business Applications' started by bleep69, Jan 10, 2011.

Not open for further replies.

Joined:
Nov 21, 2008
Messages:
135
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)

Bleep

File size:
27 KB
Views:
28
2. ### valisModerator

Joined:
Sep 24, 2004
Messages:
77,252
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.

Joined:
Nov 21, 2008
Messages:
135
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!

4. ### valisModerator

Joined:
Sep 24, 2004
Messages:
77,252
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.

Joined:
Nov 21, 2008
Messages:
135

Joined:
Nov 21, 2008
Messages:
135
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

7. ### valisModerator

Joined:
Sep 24, 2004
Messages:
77,252
pretty sure that there isn't a formula for it, but stick around, you never know what others may come up with.

8. ### bomb #21

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)+(B5*A10)

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

HTH

9. ### valisModerator

Joined:
Sep 24, 2004
Messages:
77,252
thanks, bomb......owe you one.

Joined:
Nov 21, 2008
Messages:
135
Indeed, owe you one. That's great, thank you bomb!

As Seen On