Can Excel automatically add 1 to previous cell until # is reached?

[todd774]

Is it possible to enter "1" in cell A1 & type in a formula to tell cell A2 "if A1 is < 5 then add 1". "if cell A2 (which is now 2) is < 5 then add 1". I want it to automatically add 1 to each previous cell until the number stops at 5 in cell A5. Why you might ask? ans.: Why not. I'm trying to learn. I'm not sure what such a process would be called, so I don't know how to look up an answer. Any help would be greatly appreciated.
Thank you,
Todd K.

[scott.s.fower]

you can do it with a macro, if you really want to learn then learn how to write macros, it opens doors to really powerful excel usage

also why wouldn't you use a formula like A2=A1+if(a1<5,5-a1,0) or in english if A2=A1+if a1 is less than 5, then 5-A1, else 0 meaning if it is greater than 5 it will result in A1+0 or A1

[jhren]

Without using code, you can't tell a cell to make an entry in another cell. You can howver, use a formula to make it calcute its displayed value based on another.

You can enter a conditional formula in A2 and copy it down:

=IF(A1<5,A1+1,"")

If A1 is a 1, A2 through A5 will show 2, 3, 4, and 5, respectively... A6 and down will appear blank because of the "" in the formula.

[todd774]

Thank you for all responses. So what I'm hearing is in order to make the formula calculate down to "5, I need to write a code in VBA. So I can't make this work by just writing a logic formula' Is this correct?

[Kevin UK]

Hi

If you are just going to number the cells 1-5, why don't you put this in A1 and copy down.

Please Login or Register  to view this content.

[jhren]

Just use =ROW() in cells A1:A5

[Spitzerpl]

The following formula can be copied from cell A1 as far down Column A as you want while achieving the desired results.

=IF(ROW()>5,"",IF(ROW()=1,1,MAX(OFFSET($A$1,0,0,ROW()-1,1))+1))

<Edit>Scratch my solution above. I like the MOD formula Kevin UK gave. I'll have to remember that one :-) </Edit>
If your sheet contains a header column the solution is simplier.

=IF(MAX($A$1:A1)>=5,"",MAX($A$1:A1)+1)



I like to avoid manually putting 1 in the first row because I end up deleting the 1 by mistake when I change the column's formula and fill up.

[dredwolf]

how about this:
in A2 :

Please Login or Register  to view this content.

Drag down
enter a number into A1 and if it's less than 5, it will add 1 to the number until it is 5

Hope this helps

EDIT-
if you want the cells empty till a value is entered into A1, then change the formula to this :

Please Login or Register  to view this content.

[jhren]

Man, you guys have my head spinning on something so simple. I realize it may just be a simplified analogy of what is desired, but all these formulas...

...todd774 [OT: do you guys use OP (original post) or OPr (original poster) or any other symbology to refer to thread starter?] said in OP if he enters a 1 in cell A1, and A1 is <5, can he enter a formula in A1 to add 1 to the 1 he entered and put it in A2. As I explained in post #3, you can't tell a cell (have a formula in the cell) to make a 2nd cell anything... especially if all that you put in the 1st cell is a 1. Regarding the 2nd cell, what a cell displays, with the exception using code, is always dependent on what is in the cell. Yes, it's true this 2nd cell can contain a formula which changes the value displayed dependent on the contents of another. But that's not what the OPr asked. Heck, for the time it takes to enter all these various formulas, I could have evaluated that I typed a 1 in A1, and that it is <5, and typed 2, 3, 4, and 5 in the successive cells below.

So back to the OP and OPr, what is the real purpose of your question? Or was it expressed at face value?

[dredwolf]

@ jhren, OP stated:

Is it possible to enter "1" in cell A1 & type in a formula to tell cell A2 "if A1 is < 5 then add 1". "if cell A2 (which is now 2) is < 5 then add 1". I want it to automatically add 1 to each previous cell until the number stops at 5 in cell A5

so I take that as "a value entered into the first cell, then a formula being applied to get that value to a expexted value to following cells...", maybe to see how many cells it takes to get there or some other reasoning...

[todd774]

After doing a little homework on the subject, I think what I am trying to achieve is to create a "loop" or an "algorithm". The purpose of this is just to learn. I'm self teaching myself "how to use a computer" & this particular operation is interesting to me. I want to say thanks once again for everybody's input. It's obvious that there's very intelligent people on this forum & I hope I'm asking intelligent questions.
Thanks again,
Todd K.

[todd774]

Thank you everybody for input. Like I said, this is all new to me. I understand now, what I'm trying to do is create a loop. I found some informative You Tube videos on the subject. I was able to achieve my objective through Basic Editor. This opens up new doors for me (which is my ultimate goal, to learn). Just to be clear on the subject, I'd like to know the answer to one more (stupid?) question. can such a loop be created in a logic formula without the use of Basic Editor?
Thanks again,
Todd K.

[jhren]

Depends on the logic

Some of the built-in equation functions do loops.

For example, the lookup function MATCH allows you to search for a specified value (lookup_value) in a array of cells (lookup_array) and specify whether the result must be an exact match or the closest value above or below, which requires the array to be sorted beforehand ([match_type], brackets indicate optional, but the default is implemented if omitted). The function doesn't know where a "match" is beforehand. It searches the array one cell at a time for a match. The result provided is the number of cells from starting cell of the array to the first matching cell... if unsuccesful returns an #NA error. So it essentially does an evaluation loop.

To perform logic loops you can nest a logic function within a lookup function, or nest lookup functions within other lookup functions along with the logic functions...

...but don't be suprised if you get an unexpected result Best to evaluate the root function independently, before amassing them in nested equations.

Another method is using array formulas. I leave that as an exercise for you to discover, as I have only used 'em on occasion and still do not entirely understand when and why I have to use 'em... and somewhat the how...

[dredwolf]

Excel itself does not do looping (although, through Array Formuluas and SUMPRODUCT(..)functions, you can sometimes simulate a loop style calculation), otherwise, you will need to use the VBA to create loops

[jhren]

Excel itself does not do looping...

Depends on what your definition of a loop is.