Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Continue the calculation but stops at 99 *

  1. #1
    Junior Member
    Join Date
    Jan 2017
    Posts
    11
    Rep Power
    0

    Continue the calculation but stops at 99 *

    I need to have the spreadsheet continue the calculation in column "I" which stops at 99. It would be nice if columns F, G and I were moved to C, D and E starting at C4. This was made by a friend that is no longer a friend so I can't ask her and I don't have anymore excel savvy friends. Is this an easy fix or am I asking for to much?

    Thanks for looking.
    Attached Files Attached Files
    Last edited by DocAElstein; 01-15-2019 at 09:25 PM. Reason: Changed Title to make selection of page 2 work : added a * after original 99

  2. #2
    Junior Member
    Join Date
    Jan 2017
    Posts
    11
    Rep Power
    0
    If you could instruct me on how to do what I want to accomplish, I believe I can do it. I have the bear bones basics down. So to sum up, any suggestions would be greatly appreciated.

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi learning
    Welcome to the Forum.
    On the face of it it seems very simple.
    But it is not clear to me exactly what you are asking.



    Also I appears to stop at 94 in the uploaded worksheet?


    I upload a quick attempt at what i think you might be asking:
    All I did was the following:
    _ I inserted a column at F. ( Initially I was thinking of copying all 4 columns, but then I noticed that you wanted to compress to 3 columns. So the extra column was not needed. ( By inserting the column all formulas were automatically adjusted by Excel) )
    I changed the formula in F2 to be absoulte ( adding the $'s ), then copied it to D4
    Then I copied formula G2 and I2 to E45 and F4, and adjusted so that they did the same as before, but using D4 instead of F2
    Then I copied the second data row and adjusted where necersary so they worked on the new data columns.
    Then simply selected the second row, moved the mouse to the bottom right of the selection over the small square point until a + appeared. Then you hold the left mouse down and just drag the formulas down

    Alan
    Attached Files Attached Files
    Last edited by DocAElstein; 01-23-2017 at 02:23 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #4
    Junior Member
    Join Date
    Jan 2017
    Posts
    11
    Rep Power
    0
    Thank you DocAElstein for helping me. As you can see in the picture I changed the ratio to 3 and the limit to 600. In the picture you see that it's confusing because columns G H and I are still there which are not needed because they are now C D and E. and then you see that the calculations stop at 520 in D103 and continue with 1's and 4's. I need it to continue calculating to 600 or to whatever number I put as the limit in B3chart1returnpic.jpg

  5. #5
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi Learning,
    If I change the spacing to 3 and the limit to 600, I get this:

    Values that you see:

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Spacing
    3
    2
    Start
    120
    121
    124
    1
    3
    Limit
    600
    125
    128
    2
    4
    121
    124
    1
    129
    132
    3
    5
    125
    128
    2
    133
    136
    4
    6
    129
    132
    3
    137
    140
    5
    Worksheet: Sheet1





    Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    117
    573
    576
    114
    581
    584
    116
    118
    577
    580
    115
    585
    588
    117
    119
    581
    584
    116
    589
    592
    118
    120
    585
    588
    117
    593
    596
    119
    121
    589
    592
    118
    597
    600
    120
    122
    593
    596
    119
    123
    597
    600
    120
    124
    125
    Worksheet: Sheet1



    Formulas

    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Spacing
    3
    2
    Start
    120
    =$B$2+1
    =IF(G2="","",G2+$B$1)
    =IF(G2="","",J1+1)
    3
    Limit
    600
    =IF(H2>=$B$3,"",D4+1)
    =IF(G3="","",G3+$B$1)
    =IF(G3="","",J2+1)
    4
    =$B$2+1
    =IF(C4="","",C4+$B$1)
    =IF(C4="","",E3+1)
    =IF(H3>=$B$3,"",H3+1)
    =IF(G4="","",G4+$B$1)
    =IF(G4="","",J3+1)
    5
    =IF(D4>=$B$3,"",H2+1)
    =IF(C5="","",C5+$B$1)
    =IF(C5="","",E4+1)
    =IF(H4>=$B$3,"",H4+1)
    =IF(G5="","",G5+$B$1)
    =IF(G5="","",J4+1)
    6
    =IF(D5>=$B$3,"",H3+1)
    =IF(C6="","",C6+$B$1)
    =IF(C6="","",E5+1)
    =IF(H5>=$B$3,"",H5+1)
    =IF(G6="","",G6+$B$1)
    =IF(G6="","",J5+1)
    Worksheet: Sheet1



    Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    117
    =IF(D116>=$B$3,"",H114+1)
    =IF(C117="","",C117+$B$1)
    =IF(C117="","",E116+1)
    =IF(H116>=$B$3,"",H116+1)
    =IF(G117="","",G117+$B$1)
    =IF(G117="","",J116+1)
    118
    =IF(D117>=$B$3,"",H115+1)
    =IF(C118="","",C118+$B$1)
    =IF(C118="","",E117+1)
    =IF(H117>=$B$3,"",H117+1)
    =IF(G118="","",G118+$B$1)
    =IF(G118="","",J117+1)
    119
    =IF(D118>=$B$3,"",H116+1)
    =IF(C119="","",C119+$B$1)
    =IF(C119="","",E118+1)
    =IF(H118>=$B$3,"",H118+1)
    =IF(G119="","",G119+$B$1)
    =IF(G119="","",J118+1)
    120
    =IF(D119>=$B$3,"",H117+1)
    =IF(C120="","",C120+$B$1)
    =IF(C120="","",E119+1)
    =IF(H119>=$B$3,"",H119+1)
    =IF(G120="","",G120+$B$1)
    =IF(G120="","",J119+1)
    121
    =IF(D120>=$B$3,"",H118+1)
    =IF(C121="","",C121+$B$1)
    =IF(C121="","",E120+1)
    =IF(H120>=$B$3,"",H120+1)
    =IF(G121="","",G121+$B$1)
    =IF(G121="","",J120+1)
    122
    =IF(D121>=$B$3,"",H119+1)
    =IF(C122="","",C122+$B$1)
    =IF(C122="","",E121+1)
    =IF(H121>=$B$3,"",H121+1) =IF(G122="","",G122+$B$1) =IF(G122="","",J121+1)
    123
    =IF(D122>=$B$3,"",H120+1)
    =IF(C123="","",C123+$B$1)
    =IF(C123="","",E122+1)
    =IF(H122>=$B$3,"",H122+1) =IF(G123="","",G123+$B$1) =IF(G123="","",J122+1)
    124
    =IF(D123>=$B$3,"",H121+1) =IF(C124="","",C124+$B$1) =IF(C124="","",E123+1) =IF(H123>=$B$3,"",H123+1) =IF(G124="","",G124+$B$1) =IF(G124="","",J123+1)
    125
    =IF(D124>=$B$3,"",H122+1) =IF(C125="","",C125+$B$1) =IF(C125="","",E124+1) =IF(H124>=$B$3,"",H124+1) =IF(G125="","",G125+$B$1) =IF(G125="","",J124+1)
    Worksheet: Sheet1




    Alan

    P.s. I left the original data in , ( moved across by one column ) for no particular reason - it was just useful to check the final results
    Last edited by DocAElstein; 01-23-2017 at 02:49 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #6
    Junior Member
    Join Date
    Jan 2017
    Posts
    11
    Rep Power
    0
    I'm guessing I'm not being clear with what I'm trying to accomplish. Let's go back to my original post. As you see it, I want to move columns F G and I over to C D and E, starting at C4. I want it to do what it does just start at C4 and continue no matter the limit in B3. Right now if I put 600 as the limit it stops at 417 in H100 and 99 in I100 and it should keep going to 600 and whatever the corresponding number in I column. If you go back to chart1 open it and leave the spacing at 2 and start at 120 but change the limit to 600 you'll see what I mean. I need it to continue now if there has to be a true limit then make it 999. Also I just want to move the calculations over to C column and start at C4. The "spacing, start and limit" have to be able to change, see it's a ratio like 1:3 with 3 being the spacing with whatever the start is and then the limit. So a 1:2 with a 600 limit. I suppose that the error is when it's trying to calculate the spacing with the start and the limit.
    I've beat this until I see spots, at first I thought it was in a substring at the beginning but I can't find it. I'm slowly learning through various youtube videos. Hopefully I've made it clearer. If not, I don't know what to do.

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi
    I may be missing something obvious.
    In your original File you have no formulas past row 100, so you will never get any values returned after row 100

    From the original chart 1:
    Using Excel 2007 32 bit
    Row\Col
    E
    F
    G
    H
    I
    J
    99
    =IF(G98>=$B$3,"",G98+1)
    =IF(F99="","",F99+$B$1)
    =IF(F99="","",I98+1)
    100
    =IF(G99>=$B$3,"",G99+1)
    =IF(F100="","",F100+$B$1)
    =IF(F100="","",I99+1)
    101
    102
    Worksheet: Sheet1

    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #8
    Junior Member
    Join Date
    Jan 2017
    Posts
    11
    Rep Power
    0
    Ok, so that's my problem. I thought there was a formula error when the formula has to be written. But if that's true then really all I'm doing is a lot of formula writing when I could just write it out on paper. So then it begs the question why use excel at all.......Ok, I'm done with it. Thank you for all of your effort and help. The thread can be closed, good-bye.

  9. #9
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi learning,
    Quote Originally Posted by learning View Post
    ....all I'm doing is a lot of formula writing ....
    No you do not need to write in any formulas...


    Do you know that you can just drag the formulas down as far as you want? For cell references without a $, Excel changes the formulas appropriately. They are called relative cell references, like A1


    If you add $’s like $A$1 then that fixes them, and so by dragging down they do not change. In your formulas, for example you have $B$3. That means you always use that cell value. It does not change when you drag it down. it is called an absolute reference


    Without the $’s in the cell references, the cell formulas change automatically as you drag down.
    You do not need to type in any formulas – just drag down and it fills a them in all in one go !



    To fill in a massive amount of your formulers without writing a single thing:
    _ Select, for example, F95 to I95 in the original chart
    _ Move the mouse to the small black square at bottom right of the selection. A black + should appear.
    _ Hold the left mouse down on that + and drag the formulas down as far as you want.

    DragFormulasDown.JPG http://imgur.com/H5378wI
    DragFormulasDown.JPG


    This probably explains it better than I can:
    https://support.office.com/en-us/art...3-83aa1a63e218


    Alan
    Last edited by DocAElstein; 01-23-2017 at 09:33 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #10
    Junior Member
    Join Date
    Jan 2017
    Posts
    11
    Rep Power
    0
    Ok, I did that and that's wonderful but how can I change the spreadsheet so I don't have to keep dragging it down? Is that possible? I thought that what the "limit" entry is suppose to do automatically. Isn't it?

Similar Threads

  1. TAT Calculation
    By pramodagroiya in forum Excel Help
    Replies: 5
    Last Post: 05-30-2016, 12:27 PM
  2. Replies: 1
    Last Post: 03-01-2015, 12:11 AM
  3. On Going Calculation
    By justme1052 in forum Excel Help
    Replies: 2
    Last Post: 12-31-2013, 02:06 AM
  4. Macro stops running if date is not in past...
    By Carlos Arruda in forum Excel Help
    Replies: 4
    Last Post: 03-10-2013, 04:33 PM
  5. Replies: 2
    Last Post: 09-16-2012, 02:28 AM

Posting Permissions

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