+ Reply to Thread
Results 1 to 3 of 3

Formula that returns only whole numbers & the whole numbers add up to my original number

  1. #1
    Registered User
    Join Date
    09-02-2015
    Location
    West Palm Beach, Florid
    MS-Off Ver
    2013
    Posts
    3

    Formula that returns only whole numbers & the whole numbers add up to my original number

    Hello,

    I am using this sheet as a supplement positions I have in certain stocks.

    Lets say I am buying 50 shares of stock, and I am selling out a percentage of the 50 each day for 13 days.

    Day 1 I sell 6%
    Days 2 - 6 I sell 8% each day
    Days 7-10 I sell 9% each day
    Day 11-13 i sell 6% each day

    The issue I am having is that I cannot sell 4.5 shares of a stock like the formula currently tells me to do on days 7-10. I have been going through and manually editing any days where this occurs and just making them into whole numbers manually (change 2 days to 4 shares and 2 days to 5 shares).

    Is there any way to set this formula up so that it returns only whole numbers AND equals 100% of my initial starting number?

    I have tried using rounding and INT formulas yet have no found one that accomplishes what I was looking for. The RoundUP/Down and TRUNC formulas always leave me without 100% of the initial number of shares.

    I have included an attachment showing the current method behind it.

    Thanks for any help, it is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,082

    Re: Formula that returns only whole numbers & the whole numbers add up to my original numb

    Can't you calculate the shares with the percentage as you've been doing then on the last day
    sum the all previous days (the integers) and subtract them from 50 ?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    09-02-2015
    Location
    West Palm Beach, Florid
    MS-Off Ver
    2013
    Posts
    3

    Re: Formula that returns only whole numbers & the whole numbers add up to my original numb

    Yes I can do that, and that is what I am planning on doing if I cannot find an alternative. Thanks for the suggestion

+ 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. Rounding numbers to whole number need equal original
    By Papeters111 in forum Excel General
    Replies: 6
    Last Post: 11-19-2014, 06:12 PM
  2. Replies: 1
    Last Post: 06-26-2013, 02:22 PM
  3. Replies: 1
    Last Post: 02-01-2013, 06:30 PM
  4. Multiplying odd numbers by percentages to total to original number
    By leepea42 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2013, 02:22 AM
  5. How to write a formula that returns a zero for negative numbers
    By thesongbirdsociety in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2012, 02:33 PM
  6. Replies: 3
    Last Post: 07-06-2012, 02:03 PM
  7. Excel 2007 formula returns negative numbers
    By bensonsb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-06-2012, 09:15 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