Continue the calculation but stops at 99 *

[learning]

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.

[learning]

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.

[DocAElstein]

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

[learning]

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 B31855

[DocAElstein]

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

1Spacing
3


2Start
120
121
124
1


3Limit
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

1Spacing
3


2Start
120
=$B$2+1
=IF(G2="","",G2+$B$1)
=IF(G2="","",J1+1)


3Limit
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

[learning]

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.

[DocAElstein]

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

[learning]

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.

[DocAElstein]

Hi learning,

....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
1856


This probably explains it better than I can:
https://support.office.com/en-us/article/Copy-a-formula-by-dragging-the-fill-handle-in-Excel-2016-for-Mac-dd928259-622b-473f-9a33-83aa1a63e218


Alan

[learning]

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?

[DocAElstein]

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?

You could do that with a VBA Code.
But that is realy trying to run before you can walk.
Can you not just drag the formulas a long way down so that there is always enough. ?

The "limit" entry is just a value used in the formula.
The formulas give you no value ( "" ) or a value based on an equation depending on If the the condition is met
https://support.office.com/en-us/article/IF-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

[DocAElstein]

.... I thought that what the "limit" entry is suppose to do automatically. Isn't it?

Look at this for example

Using Excel 2007 32 bit
Row\Col
F

95
=IF(G94>=$B$3,"",G94+1)
Worksheet: Sheet1

IF the value of cell G94 is greater than or equal to the calue in B3 ( the limit ) then the formula returns a value in the cell of ""
"" is just Excel's way of saying nothing.
Otherwise if that condition is not met the value returned to the cell is given by
( the value in cell G94 ) +1

[learning]

This is not the thread I started, what happened? It looks like mine was hijacked. This is not a continuation of page 1










_.___________________________________

Moderator Note ( 15th Jan 2019 ): Changed title so that clicking on page 2 works... added a * after 99 in original title

[learning]

Hello,????

[learning]

Ok, thanks again. I think at this point is to go to fiverr and find someone to fix it the way I want it for 5 dollars and just be done with it. Thanks again.

[DocAElstein]

Hello,????

Hello :)

[snb]

Ok, thanks again. I think at this point is to go to fiverr and find someone to fix it the way I want it for 5 dollars and just be done with it. Thanks again.

In that case you are not learning, Learning.

[learning]

I guess you're right snb, thanks for the observation.

[learning]

See snb,
It's a need vs. want vs. have to situation
I don't need or have to learn excel
I want to learn excel
Just because I paid someone to fix my spreadsheet doesn't mean I've stopped wanting to learn excel.
See there you posted a judgement like you know me and you don't.
Thanks again.