1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

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

Thread Status:
Not open for further replies.
Advertisement
  1. bleep69

    bleep69 Thread Starter

    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)

    Thanks in advance!

    Bleep
     

    Attached Files:

  2. valis

    valis Moderator

    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.
     
  3. bleep69

    bleep69 Thread Starter

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

    valis Moderator

    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.
     
  5. bleep69

    bleep69 Thread Starter

    Joined:
    Nov 21, 2008
    Messages:
    135
    Ok, thanks for your efforts!
     
  6. bleep69

    bleep69 Thread Starter

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

    valis Moderator

    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

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

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

    HTH :)
     
  9. valis

    valis Moderator

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

    bleep69 Thread Starter

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

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/973752

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice