+ Reply to Thread
Results 1 to 2 of 2

Tricky Excel problem: spreading amounts over user inputted number of columns

  1. #1
    Registered User
    Join Date
    01-13-2017
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2013
    Posts
    1

    Tricky Excel problem: spreading amounts over user inputted number of columns

    I've run into a tricky problem which I think I have an inelegant solution for, but wanted to see if there was something cleaner. It takes a bit of explaining so please bear with me:

    The problem essentially boils down to this: think of 2 inputs, how much money I get in a given day, and how long that money lasts me. A simple version of the model looks like this:
    • Input 1 - how many days money lasts me: 3
    • Input 2 - amount of money received on day 1: $300

    The output would essentially be a bunch of columns representing day 1 to day 20, and depending on my inputs, they show how much money I can spend each day. Let's assume for simplicity that that amount is just amount received divided by days.

    This is the first area of trickiness, I it's not enough for me to put $300/3 in each cell, but the cells need to know when the 3 days has been reached, and if someone put 5 instead, they need to similarly now divide $300/5 but now show up in 5 cells, with the remaining days showing 0.

    If it was simply this I think it would be a relatively easy solution, but the tricky thing is the way I have it, it's not just day 1 where I input how much money, but imagine now I have random payments coming in that the user can input (e.g. ok $300 day 1, $500 day 3, $1000 day 7). Thankfully the number of days it lasts me will be consistent. So as an example, this is what I would expect to get spit out with the following inputs:
    • # of days money lasts: 4
    • Day #1 money received: $100
    • Day #3 money received: $500
    • Day #4 money received: $1000

    Should spit out the following values:
    • Day #1 money to spend: $25 ($100/4)
    • Day #2 money to spend: $25 ($100/4)
    • Day #3 money to spend: $150 ($100/4 + $500/4)
    • Day #4 money to spend: $400 ($100/4 + $500/4 + $1000/4)
    • Day #5 money to spend: $375 ($500/4 + $1000/4)
    • Day #6 money to spend: $375 ($500/4 + $1000/4)
    • Day #7 money to spend: $250 ($1000/4)

    Hopefully this makes the problem clear. Right now my solution is to make a pretty big table (20x20 if it's 20 days), but I'm not sure if there's a more elegant solution. Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,104

    Re: Tricky Excel problem: spreading amounts over user inputted number of columns

    Hi, welcome to the forum

    I think I understand what you want, but it would probably be better if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 09-28-2016, 12:01 PM
  2. Insert number of user inputted rows without copying pre-existing data
    By KT99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2014, 02:55 PM
  3. [SOLVED] Add Invoice Amounts and Paid Amounts based on user inputs.
    By s2jrchoi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-18-2013, 01:49 PM
  4. Very Tricky problem in EXCEL
    By sramidi in forum Excel General
    Replies: 1
    Last Post: 01-23-2012, 05:31 AM
  5. Tricky Excel mailout problem...
    By Whizzo in forum Excel General
    Replies: 1
    Last Post: 04-07-2009, 08:37 AM
  6. Using VBA how do I return the column number of first column in a user inputted range?
    By NukedWhale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2009, 06:25 PM
  7. [SOLVED] Need help with a tricky problem of placing a number within an array...
    By matthew.webb@net1.demon.co.uk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-23-2006, 05:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1