Printable View
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.
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.
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
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 B3Attachment 1855
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
Worksheet: Sheet1
Row\Col Spacing Start Limit
Using Excel 2007 32 bit
Worksheet: Sheet1
Row\Col
Formulas
Using Excel 2007 32 bit
Worksheet: Sheet1
Row\Col Spacing Start Limit
Using Excel 2007 32 bit
Worksheet: Sheet1
Row\Col =IF(H121>=$B$3,"",H121+1) =IF(G122="","",G122+$B$1) =IF(G122="","",J121+1) =IF(H122>=$B$3,"",H122+1) =IF(G123="","",G123+$B$1) =IF(G123="","",J122+1) =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) =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)
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
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.
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
Worksheet: Sheet1
Row\Col
Alan
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.
Hi learning,
No you do not need to write in any formulas...Quote:
Originally Posted by learning
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
Attachment 1856
This probably explains it better than I can:
https://support.office.com/en-us/art...3-83aa1a63e218
Alan
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.Quote:
Originally Posted by learning
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/art...c-aa8bbff73be2
Look at this for exampleQuote:
Originally Posted by learning
Using Excel 2007 32 bit
Worksheet: Sheet1
Row\Col
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
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
Hello,????
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.
Hello :)Quote:
Originally Posted by learning
In that case you are not learning, Learning.Quote:
Originally Posted by learning
I guess you're right snb, thanks for the observation.
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.