# 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

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

#### Attachments

• 27 KB Views: 28

#### valis

Moderator
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

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

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
pretty sure that there isn't a formula for it, but stick around, you never know what others may come up with.

#### bomb #21

"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

#### valis

Moderator
thanks, bomb......owe you one.

#### bleep69

Indeed, owe you one. That's great, thank you bomb!

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.

As Seen On