Should i use IF statements?

[petethecat]

Hi All,
I have a sheet where i have a column with mix of Letters & numbers. in another column I want to assign a number to the letter (in this instance it is an "R" & i want to give this a value of 1) & i want to increase the values of the occupied cells by one. So i want to change a 1 to a two, a 2 to a three etc. To complicate maters i have a series of Zero values that i also want to assign a value to but i want to give them a value next in sequence AFTER the other numbers. So if my last number is a 5 (now updated to a six) i want the next value to be 7.

I have tried using a long IF statement but it is not really working 100%.
=IF(K19=0,0,IF(K19="R",1,IF(K19=1,2,IF(K19=2,3,IF(K19=3,4,IF(K19=4,5,IF(K19-5,6,IF(K19=6,7,IF(K19=7,8,IF(K19=8,9,IF(K19=9,10)))))))))))

This of course doesn't deal with the zeros in any useful way.

I am a bit of a novice with this kind of thing so any help would be appreciated!

 

[DanteAmor]

Assuming the formula is in cell L19, put the following:

=IF(K19=0,IF(ISNUMBER(L18),L18+1,0),IF(K19="R",1,IF(K19=1,2,IF(K19=2,3,IF(K19=3,4,IF(K19=4,5,IF(K19-5,6,IF(K19=6,7,IF(K19=7,8,IF(K19=8,9,IF(K19=9,10)))))))))))

Also try:

=IF(K19=0,IF(ISNUMBER(L18),L18+1,0),IF(K19="R",1,K19+1))

 

[petethecat]

Assuming the formula is in cell L19, put the following:

=IF(K19=0,IF(ISNUMBER(L18),L18+1,0),IF(K19="R",1,IF(K19=1,2,IF(K19=2,3,IF(K19=3,4,IF(K19=4,5,IF(K19-5,6,IF(K19=6,7,IF(K19=7,8,IF(K19=8,9,IF(K19=9,10)))))))))))

Also try:

=IF(K19=0,IF(ISNUMBER(L18),L18+1,0),IF(K19="R",1,K19+1))

The second one almost works! All the zeros are coming back with a one. I want them to be the next number available in the sequence (if that makes any sense!). So if my last converted number is a five i want the first zero to be a six & so on.

As before - any help is greatly appreciated!

 

[jtakw]

Hi,

It would be much better if you show some sample data with expected results along with your description.
Don't know if this is what you mean:

Book1
	A	B
1	1	2
2	3	4
3	6	7
4	R	1
5	0	8
6	2	3
7	R	1
8	9	10
9	0	11
10	0	11
11	5	6
Sheet620

Cell Formulas
Range	Formula
B1	=IF(A1="R",1,IF(A1=0,MAX(A$1:A1)+2,A1+1))

 

[petethecat]

Hi,

It would be much better if you show some sample data with expected results along with your description.
Don't know if this is what you mean:

	A	B
1	1	2
2	3	4
3	6	7
4	R	1
5	0	8
6	2	3
7	R	1
8	9	10
9	0	11
10	0	11
11	5	6

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet620​

Worksheet Formulas Cell Formula B1 =IF(A1="R",1,IF(A1=0,MAX(A$1:A1)+2,A1+1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

This looks better - but i am still getting mixed results with the zeros

my column looks like this:

data	expected results
2	3
0	7
4	5
5	6
1	2
3	8
0	9
0	10
0	11
0	12
R	1

<colgroup><col><col></colgroup><tbody>
</tbody>

what i am actually getting is this:

data	actual
2	3	IF(K19="R",1,IF(K19=0,MAX(K$19:K19)+2,K19+1))
0	4	IF(K20="R",1,IF(K20=0,MAX(K$19:K20)+2,K20+1))
4	5	IF(K21="R",1,IF(K21=0,MAX(K$19:K21)+2,K21+1))
5	6	IF(K22="R",1,IF(K22=0,MAX(K$19:K22)+2,K22+1))
1	2	IF(K23="R",1,IF(K23=0,MAX(K$19:K23)+2,K23+1))
3	4	IF(K24="R",1,IF(K24=0,MAX(K$19:K24)+2,K24+1))
0	7	IF(K25="R",1,IF(K25=0,MAX(K$19:K25)+2,K25+1))
0	7	IF(K26="R",1,IF(K26=0,MAX(K$19:K26)+2,K26+1))
0	7	IF(K27="R",1,IF(K27=0,MAX(K$19:K27)+2,K27+1))
0	7	IF(K28="R",1,IF(K28=0,MAX(K$19:K28)+2,K28+1))
R	1	IF(K29="R",1,IF(K29=0,MAX(K$19:K29)+2,K29+1))

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="2" style="width:48pt"></colgroup><tbody>
</tbody>

so i have a zero returning a 4 & the other zeros returning a 7

I notice the data range is increasing with each row down? (In bold) Should this be the full data range for each formula or should it be one range for one cell?

 

[jtakw]

Still don't understand, why did your 3 become an 8 in your expected results ?

 

[jtakw]

Assuming that 8 is a typo, and should be a 4, try this:

Book1
	A	B
1	2	3
2	0	7
3	4	5
4	5	6
5	1	2
6	3	4
7	0	8
8	0	9
9	0	10
10	0	11
11	R	1
Sheet620

Cell Formulas
Range	Formula
B1	=IF(A1="R",1,IF(A1=0,MAX(A$1:A$11)+COUNTIF(A$1:A1,0)+1,A1+1))

 

[petethecat]

Assuming that 8 is a typo, and should be a 4, try this:

	A	B
1	2	3
2	0	7
3	4	5
4	5	6
5	1	2
6	3	4
7	0	8
8	0	9
9	0	10
10	0	11
11	R	1

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet620​

Worksheet Formulas Cell Formula B1 =IF(A1="R",1,IF(A1=0,MAX(A$1:A$11)+COUNTIF(A$1:A1,0)+1,A1+1)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Thats the one! - REALLY appreciate your help.

 

[jtakw]

You're welcome.

 

[petethecat]

Hello Again. A little while ago u kindly helped me with the above post. Things have moved on since i last spoke. There are times when the letter "R" is not included so i wondered what change i need to make to the formula to accommodate this? IE if the "R" IS included in the list then "R" is the first, 1 is the second etc - exactly as per the formula. If the the letter "R" is NOT included then number one would remain 1, 2 would remain 2 etc. Can u offer any guidance?