Help with formula for Microsoft excel

  • Thread starter Thread starter Roger Govier
  • Start date Start date
Hello,
I am currently having some problems in getting a formula to work the way I
want it to. Lets say in column a row a1 I have 716+70. In column a row a2 I
would like 716+80 and in column a row a3 I want 716+90 etc for the
remaining. What I would like is that it constantly add 10 to each row.
Can someone help me out with this.
Thanks
 
mark said:
Lets say in column a row a1 I have 716+70. In column a
row a2 I would like 716+80 and in column a row a3 I want
716+90 etc for the remaining. What I would like is that
it constantly add 10 to each row.
Click to expand...

It is difficult to understand your requirements because of your confusing
terminology.

If you mean that the cell (not row) A1 has the formula =716+70, not the text
"716+70", then perhaps the following meets your needs.

Put =716+70 into A1. Put =A1+10 into A2. And copy A2 down the column for
as many cells as your need.
 
Roger Govier said:
Hi Mark

in A1
=716+Rows($1:1)*10
Copy down as far as required
Click to expand...
Ok, I got that put where do I put the beginning number 716+70 to start the
calculation?
 
Ok, I got that put where do I put the beginning number 716+70 to start the
calculation?
Click to expand...

To get 786 in A1 as an equation, perhaps a slight modification

=716+(60+10*ROW())

However, Joe's approach seems a little easier. :>)

= = = = = = =
HTH :>)
Dana DeLouis
 
Joe User said:
It is difficult to understand your requirements because of your confusing
terminology.

If you mean that the cell (not row) A1 has the formula =716+70, not the text
"716+70", then perhaps the following meets your needs.

Put =716+70 into A1. Put =A1+10 into A2. And copy A2 down the column for
as many cells as your need.
Click to expand...
Ok, sorry for the confusion but 716+70, 716+80, 716+90 etc is not a formula
it is a engineering station of a roadway? So I would I make this work?
 
Dana DeLouis said:
To get 786 in A1 as an equation, perhaps a slight modification

=716+(60+10*ROW())

However, Joe's approach seems a little easier. :>)

= = = = = = =
HTH :>)
Dana DeLouis
Click to expand...

Ok, sorry for the confusion but 716+70, 716+80, 716+90 etc is not a formula
it is a engineering station of a roadway? So I would I make this work?
 
Hi Mark

If you want it as an incrementing text value, then use
=716&"+"&ROWS($1:1)*10

enter that formula in cell A1 of your sheet and copy down as far as
required.
 
Another method to consider... put 71670 in your first cell, 71680 in your
second cell, select both cells and copy down as far as you like... then
Custom Format the cells using 0+00 for the format pattern. This way, your
station number will be actual numbers and you will be able to perform math
(addition/subtraction probably) with them.
 
mark said:
Ok, sorry for the confusion but 716+70, 716+80, 716+90
etc is not a formula it is a engineering station of a
roadway? So I would I make this work?
Click to expand...

Try the following in A1 and copy down as needed:

"716+" & (60 + 10*ROWS($1:1))


----- original message -----
 
Prof Wonmug said:
Hello,
I am currently having some problems in getting a formula to work the way I
want it to. Lets say in column a row a1 I have 716+70. In column a row a2 I
would like 716+80 and in column a row a3 I want 716+90 etc for the
remaining. What I would like is that it constantly add 10 to each row.
Can someone help me out with this.
Thanks
Click to expand...

Mark,

I think the "+" sign is confusing everyone. It looks like a formula,
but it is really just civil engineering notation. Is that correct?

If so, that what you have is a literal ("716") and a series of numbers
(10, 20, 30...). You want to add 10 to the number part and keep the
literal part as a literal.

Try this. Put "10" in A1 and "=A1+10" in A2. Then copy A2 down for as
many rows as you need. The cells should contain:

A
1 10
2 =A1+10
3 =A2+10
4 =A3+10
5 =A4+10

and should look like this:

A
1 10
2 20
3 30
4 40
5 50

This is the numeric part. We can add the literal part nwith
formatting. Select all of the cells, right-click, and select Format
cells... Click on Custom format (at the bottom) and type ["716+"0] not
including the brackets. Now your cells should display as:

A
1 716+10
2 716+20
3 716+30
4 716+40
5 716+50

Is that what you wanted?

There are ways to make the "716" and the "10" variable.
Click to expand...
Hello,
Thanks thats exactly what I want and it is working almost perfectly but when
it gets to 716+100, 716+110,716+120 etc I dont want it like that I would
like 716+100 to be 717+00 and 716+110 to be 717+10 and 716+110 to be 717+10
etc.
 
I would suggest you give the method I posted a try.

--
Rick (MVP - Excel)



mark said:
Prof Wonmug said:
Hello,
I am currently having some problems in getting a formula to work the way I
want it to. Lets say in column a row a1 I have 716+70. In column a row
a2 I
would like 716+80 and in column a row a3 I want 716+90 etc for the
remaining. What I would like is that it constantly add 10 to each row.
Can someone help me out with this.
Thanks
Click to expand...

Mark,

I think the "+" sign is confusing everyone. It looks like a formula,
but it is really just civil engineering notation. Is that correct?

If so, that what you have is a literal ("716") and a series of numbers
(10, 20, 30...). You want to add 10 to the number part and keep the
literal part as a literal.

Try this. Put "10" in A1 and "=A1+10" in A2. Then copy A2 down for as
many rows as you need. The cells should contain:

A
1 10
2 =A1+10
3 =A2+10
4 =A3+10
5 =A4+10

and should look like this:

A
1 10
2 20
3 30
4 40
5 50

This is the numeric part. We can add the literal part nwith
formatting. Select all of the cells, right-click, and select Format
cells... Click on Custom format (at the bottom) and type ["716+"0] not
including the brackets. Now your cells should display as:

A
1 716+10
2 716+20
3 716+30
4 716+40
5 716+50

Is that what you wanted?

There are ways to make the "716" and the "10" variable.
Click to expand...
Hello,
Thanks thats exactly what I want and it is working almost perfectly but
when
it gets to 716+100, 716+110,716+120 etc I dont want it like that I would
like 716+100 to be 717+00 and 716+110 to be 717+10 and 716+110 to be
717+10
etc.
Click to expand...
 
mark said:
when it gets to 716+100, 716+110,716+120 etc I dont
want it like that I would like 716+100 to be 717+00
and 716+110 to be 717+10 and 716+110 to be 717+10 etc.
Click to expand...

The simplest solution might be to combine Rick's suggestion with mine, to
wit....

Put 71670 in A1. Put =A1+10 into A2 and copy down. Format column A or the
range A1:A10 (or whatever) with Custom 0+00.

Caveat: Although a "+" appears in the cell, it is not part of the cell
value. So, for example, you could never do FIND("+",A1).


----- original message -----

mark said:
Prof Wonmug said:
Hello,
I am currently having some problems in getting a formula to work the way I
want it to. Lets say in column a row a1 I have 716+70. In column a row
a2 I
would like 716+80 and in column a row a3 I want 716+90 etc for the
remaining. What I would like is that it constantly add 10 to each row.
Can someone help me out with this.
Thanks
Click to expand...

Mark,

I think the "+" sign is confusing everyone. It looks like a formula,
but it is really just civil engineering notation. Is that correct?

If so, that what you have is a literal ("716") and a series of numbers
(10, 20, 30...). You want to add 10 to the number part and keep the
literal part as a literal.

Try this. Put "10" in A1 and "=A1+10" in A2. Then copy A2 down for as
many rows as you need. The cells should contain:

A
1 10
2 =A1+10
3 =A2+10
4 =A3+10
5 =A4+10

and should look like this:

A
1 10
2 20
3 30
4 40
5 50

This is the numeric part. We can add the literal part nwith
formatting. Select all of the cells, right-click, and select Format
cells... Click on Custom format (at the bottom) and type ["716+"0] not
including the brackets. Now your cells should display as:

A
1 716+10
2 716+20
3 716+30
4 716+40
5 716+50

Is that what you wanted?

There are ways to make the "716" and the "10" variable.
Click to expand...
Hello,
Thanks thats exactly what I want and it is working almost perfectly but
when
it gets to 716+100, 716+110,716+120 etc I dont want it like that I would
like 716+100 to be 717+00 and 716+110 to be 717+10 and 716+110 to be
717+10
etc.
Click to expand...
 
Caveat: Although a "+" appears in the cell, it is not part of the cell
value. So, for example, you could never do FIND("+",A1).
Click to expand...

But, assuming you would use FIND to retrieve either the whole station or the
plus offset from it, you could do this...

Whole Station: INT(A1/100)

Plus Offset: MOD(A1,100)

My personal preference, by the way, would be to *not* use formulas to
populate these values when you can just as easily fill the column with
constants.

--
Rick (MVP - Excel)



Joe User said:
mark said:
when it gets to 716+100, 716+110,716+120 etc I dont
want it like that I would like 716+100 to be 717+00
and 716+110 to be 717+10 and 716+110 to be 717+10 etc.
Click to expand...

The simplest solution might be to combine Rick's suggestion with mine, to
wit....

Put 71670 in A1. Put =A1+10 into A2 and copy down. Format column A or
the range A1:A10 (or whatever) with Custom 0+00.

Caveat: Although a "+" appears in the cell, it is not part of the cell
value. So, for example, you could never do FIND("+",A1).


----- original message -----

mark said:
Prof Wonmug said:
Hello,
I am currently having some problems in getting a formula to work the
way I
want it to. Lets say in column a row a1 I have 716+70. In column a row
a2 I
would like 716+80 and in column a row a3 I want 716+90 etc for the
remaining. What I would like is that it constantly add 10 to each row.
Can someone help me out with this.
Thanks

Mark,

I think the "+" sign is confusing everyone. It looks like a formula,
but it is really just civil engineering notation. Is that correct?

If so, that what you have is a literal ("716") and a series of numbers
(10, 20, 30...). You want to add 10 to the number part and keep the
literal part as a literal.

Try this. Put "10" in A1 and "=A1+10" in A2. Then copy A2 down for as
many rows as you need. The cells should contain:

A
1 10
2 =A1+10
3 =A2+10
4 =A3+10
5 =A4+10

and should look like this:

A
1 10
2 20
3 30
4 40
5 50

This is the numeric part. We can add the literal part nwith
formatting. Select all of the cells, right-click, and select Format
cells... Click on Custom format (at the bottom) and type ["716+"0] not
including the brackets. Now your cells should display as:

A
1 716+10
2 716+20
3 716+30
4 716+40
5 716+50

Is that what you wanted?

There are ways to make the "716" and the "10" variable.
Click to expand...
Hello,
Thanks thats exactly what I want and it is working almost perfectly but
when
it gets to 716+100, 716+110,716+120 etc I dont want it like that I would
like 716+100 to be 717+00 and 716+110 to be 717+10 and 716+110 to be
717+10
etc.
Click to expand...
Click to expand...
 
Back
Top