+ Reply to Thread
Results 1 to 4 of 4

Iterate subtraction until just before it becomes negative

  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    Auburn, Alabama
    MS-Off Ver
    Excel 2010
    Posts
    2

    Iterate subtraction until just before it becomes negative

    I'm trying to build a sheet that takes the odometer number from a car, and tells how many miles are left until each maintenance operation.
    To do this, I think I need to take the odometer value (henceforth x), and subtract the repair interval from it, (for example, 7500 mi, henceforth y) and either have it stop subtracting at the last iteration before the number hits 0, or have the function add y back to it once and stop, displaying the final value.
    So, mathematically,

    Iterate (x-y)=z through (z-y) until (z-y)<0, then either take the last positive value of z, or add y back to the first negative value of z so that [(z+y)=h], h>0.

    Any ideas?

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Iterate subtraction until just before it becomes negative

    Try something like this:
    A1: odometer reading
    A2: repair interval
    then use this:
    =(Int(A1/A2)+1)*A2-A1
    This should give you how many mile until next maintenance is required

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    03-05-2013
    Location
    Auburn, Alabama
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Iterate subtraction until just before it becomes negative

    That appears to work. When it reaches (in the example I used) 7499, the function displays '1'. When it reaches 7500, the function resets back to yield 7500. Now to apply conditional formatting to it P: Thanks!

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Iterate subtraction until just before it becomes negative

    You are welcome

    Please remember to mark the thread as solved if you are satisfied with your solution :
    To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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