Should i use IF statements?

P

petethecat

Board Regular
Joined
Oct 25, 2011
Messages
63
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
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))
 
Last edited:
Upvote 0
DanteAmor said:
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))
Click to expand...

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!
 
Upvote 0
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
AB
112
234
367
4R1
508
623
7R1
8910
9011
10011
1156
Sheet620
Cell Formulas
RangeFormula
B1=IF(A1="R",1,IF(A1=0,MAX(A$1:A1)+2,A1+1))
 
Upvote 0
jtakw said:
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:

AB
R
R

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

</tbody>
Sheet620​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(A1="R",1,IF(A1=0,MAX(A$1:A1)+2,A1+1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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

my column looks like this:

[TABLE="width: 190"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]data[/TD]
[TD]expected results[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]1


[/TD]
[/TR]
</tbody>[/TABLE]
what i am actually getting is this:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]data[/TD]
[TD="width: 64"] actual[/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl69"]2[/TD]
[TD="class: xl70"]3[/TD]
[TD]IF(K19="R",1,IF(K19=0,MAX(K$19:K19)+2,K19+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]0[/TD]
[TD="class: xl70"]4[/TD]
[TD]IF(K20="R",1,IF(K20=0,MAX(K$19:K20)+2,K20+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]4[/TD]
[TD="class: xl70"]5[/TD]
[TD]IF(K21="R",1,IF(K21=0,MAX(K$19:K21)+2,K21+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]5[/TD]
[TD="class: xl70"]6[/TD]
[TD]IF(K22="R",1,IF(K22=0,MAX(K$19:K22)+2,K22+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]1[/TD]
[TD="class: xl70"]2[/TD]
[TD]IF(K23="R",1,IF(K23=0,MAX(K$19:K23)+2,K23+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]3[/TD]
[TD="class: xl70"]4[/TD]
[TD]IF(K24="R",1,IF(K24=0,MAX(K$19:K24)+2,K24+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]0[/TD]
[TD="class: xl70"]7[/TD]
[TD]IF(K25="R",1,IF(K25=0,MAX(K$19:K25)+2,K25+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]0[/TD]
[TD="class: xl70"]7[/TD]
[TD]IF(K26="R",1,IF(K26=0,MAX(K$19:K26)+2,K26+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]0[/TD]
[TD="class: xl70"]7[/TD]
[TD]IF(K27="R",1,IF(K27=0,MAX(K$19:K27)+2,K27+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]0[/TD]
[TD="class: xl70"]7[/TD]
[TD]IF(K28="R",1,IF(K28=0,MAX(K$19:K28)+2,K28+1))[/TD]
[/TR]
[TR]
[TD="class: xl69"]R[/TD]
[TD="class: xl70"]1[/TD]
[TD]IF(K29="R",1,IF(K29=0,MAX(K$19:K29)+2,K29+1))[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"></colgroup><tbody>[TR]
[TD="class: xl69"][/TD]
[TD="class: xl70"]

[/TD]
[/TR]
</tbody>[/TABLE]
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?
 
Upvote 0
Still don't understand, why did your 3 become an 8 in your expected results ?
 
Upvote 0
Assuming that 8 is a typo, and should be a 4, try this:


Book1
AB
123
207
345
456
512
634
708
809
9010
10011
11R1
Sheet620
Cell Formulas
RangeFormula
B1=IF(A1="R",1,IF(A1=0,MAX(A$1:A$11)+COUNTIF(A$1:A1,0)+1,A1+1))
 
Last edited:
Upvote 0
jtakw said:
Assuming that 8 is a typo, and should be a 4, try this:

AB
R

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]

</TBODY>
Sheet620​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(A1="R",1,IF(A1=0,MAX(A$1:A$11)+COUNTIF(A$1:A1,0)+1,A1+1))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...


Thats the one! - REALLY appreciate your help.
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Assign value to array element and use ARRAYTOTEXT function
Replies
1
Views
83
gifariz
G
R
  • Question Question
Add a code or number at the end of the file name using VBA (Looped)
Replies
3
Views
122
HighAndWilder
H
M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
176
dreid1011
dreid1011
Q
  • Question Question
use row number as reference for edit value in a row VBA
Replies
4
Views
298
Alex Blakenburg
A
W
  • Solved
Using MIN with IF resulting in #VALUE error
2
Replies
15
Views
1K
myall_blues
myall_blues

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top