+ Reply to Thread
Results 1 to 10 of 10

Needing a formula to calculate increments of 0.10 every 6 days.

  1. #1
    Registered User
    Join Date
    07-12-2019
    Location
    Austin, TX
    MS-Off Ver
    Office 365, but downloaded to computer not web version
    Posts
    5

    Question Needing a formula to calculate increments of 0.10 every 6 days.

    Basically I'm calculating $0.75 for 6 days then on the 7th day it changes to $0.85 then 6 days later 7th day $0.95 and I need it to do it for 365 days.

    I've tried
    =IF(MOD(ROWS($B$1:B1), 6)=0.75,B1+0.1, B1)

    and

    =INT((ROW(B1)+0.1)/6)+0.1

    and they work as they are supposed too, but my increments go from 0.75 to 1.75 to 2.75 and so on and the increments of 0.10 goes from 0.10 for 6 days to 1.10. But I need it to start out at 0, then 7th day .10, then 6 days 7th day .10 again.
    Last edited by Rubicon2020; 07-12-2019 at 02:19 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    Can you provide an example of desired results?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-12-2019
    Location
    Austin, TX
    MS-Off Ver
    Office 365, but downloaded to computer not web version
    Posts
    5

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    $0.75
    $0.75
    $0.75
    $0.75
    $0.75
    $0.75
    $0.85
    $0.85
    $0.85
    $0.85
    $0.85
    $0.85
    $0.95
    $0.95
    $0.95
    $0.95
    $0.95
    $0.95
    $1.05
    and so on for 365 days. I hope that's what you are asking for.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    A
    B
    1
    Fee
    2
    0.75
    A2: Input
    3
    0.75
    A3: =A2 + 0.1 * (MOD(ROWS(A$2:A3) - 1, 6) = 0)
    4
    0.75
    5
    0.75
    6
    0.75
    7
    0.75
    8
    0.85
    9
    0.85
    10
    0.85
    11
    0.85
    12
    0.85
    13
    0.85
    14
    0.95
    15
    0.95
    16
    0.95
    17
    0.95
    18
    0.95
    19
    0.95
    20
    1.05
    21
    1.05
    22
    1.05
    23
    1.05
    24
    1.05

  5. #5
    Registered User
    Join Date
    07-12-2019
    Location
    Austin, TX
    MS-Off Ver
    Office 365, but downloaded to computer not web version
    Posts
    5

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    So if I type what you wrote on B3 and grab tail and run free it will continue to calculate all the way for 365 days?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    Or ...

    A
    B
    1
    Fee
    2
    0.75
    A2: =0.75 + 0.1 * INT((ROWS(A$2:A2) - 1) / 6)
    3
    0.75
    4
    0.75
    5
    0.75
    6
    0.75
    7
    0.75
    8
    0.85
    9
    0.85
    10
    0.85
    11
    0.85
    12
    0.85
    13
    0.85
    14
    0.95
    15
    0.95
    16
    0.95
    17
    0.95
    18
    0.95
    19
    0.95
    20
    1.05
    21
    1.05
    22
    1.05
    23
    1.05
    24
    1.05

  7. #7
    Registered User
    Join Date
    07-12-2019
    Location
    Austin, TX
    MS-Off Ver
    Office 365, but downloaded to computer not web version
    Posts
    5

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    And now I can't get the function F4 to work (I'm on Mac) to add the $ $ to the formula.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    You could copy the formula from the post ...

  9. #9
    Registered User
    Join Date
    07-12-2019
    Location
    Austin, TX
    MS-Off Ver
    Office 365, but downloaded to computer not web version
    Posts
    5

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    Got it! Thank You SO MUCH!!!! A life saver!

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Needing a formula to calculate increments of 0.10 every 6 days.

    You're welcome.

+ 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. [SOLVED] Formula to calculate working days and calendar days per month-year ?
    By donny007 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-05-2018, 06:23 PM
  2. Increments for days/months on y-axis
    By Matlas in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-15-2018, 11:37 AM
  3. [SOLVED] calculate total number of days between two days based on 365 days year
    By aparunkumar in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-14-2016, 12:51 AM
  4. Replies: 3
    Last Post: 01-21-2016, 06:07 PM
  5. Calculate # of days open with increments of 30, 60 & 90 day values
    By skgates in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-14-2012, 10:10 AM
  6. Formula help to calculate only the first 90 days
    By avidcat in forum Excel General
    Replies: 4
    Last Post: 01-19-2009, 05:17 PM
  7. [SOLVED] Date increments by X number of days
    By sweens319 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2005, 07:55 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