0
=ModuleNum&"."&SectionNum&"."&Sub1_SectionNum&"."&Sub2_SectionNum&"."&Sub3_SectionNum&"."&Sub4_SectionNum&"."&Sub5_SectionNum&"."&Sub6_SectionNum


IF SectionNum >= 0
THEN &"."&Sub1_SectionNum

IF Sub1_SectionNum != Null
THEN &"."&Sub2_SectionNum

IF Sub2_SectionNum != Null
THEN &"."&Sub3_SectionNum

IF Sub3_SectionNum != Null
THEN &"."&Sub4_SectionNum

IF Sub4_SectionNum != Null
THEN &"."&Sub5_SectionNum

IF Sub5_SectionNum != Null
THEN &"."&Sub6_SectionNum

I have a formula like the one above, and a lot of times it is giving me results like "10.0....." instead of what I'd like 10.0 or 2.4.5.6.1.3.7.1. I would like to make it conditional to handle both cases and I think this will require a nested conditional formula. I need some assistance with the nesting.

For example, I was able to write the formula

=ModuleNum&"."&IF(SectionNum>=0,SectionNum,"F")&"."&IF(Sub1_SectionNum>0,Sub1_SectionNum,"F")&"."&IF(Sub2_SectionNum>0,Sub2_SectionNum,"F")&"."&IF(Sub3_SectionNum>0,Sub3_SectionNum,"F")&"."&IF(Sub4_SectionNum>0,Sub4_SectionNum,"F")&"."&IF(Sub5_SectionNum>0,Sub5_SectionNum,"F")&"."&IF(Sub6_SectionNum>0,Sub6_SectionNum,"F")

which adds the conditional portion, but you will see that if the condition is not greater than 0, it will show .F instead of nothing. I would like for if the condition is not greater than 0 for the statement to stop evaluating.

I think I want something like this,

=ModuleNum & IF(SectionNum>=0,&"."&SectionNum,"F")

But I can't seem to get it to evaluate in my xlsx

Much Appreciated!

  • Are the SectionNums you want to ignore always blank? Or do you want 10.0.0.0.0.0.0 to evaluate to 10? – Aron Foster Oct 15 '13 at 22:31
  • Only ModuleNum and SectionNum are required, so if ModuleNum is 2 and SectionNum is 0, I'd like it to evalulate to 2.0. Sub#_SectionNums can be blank. Thanks! – jg100309 Oct 16 '13 at 12:36
0

This was my first instinct:

x = SectionNum&"."&Sub1_SectionNum&"."&Sub2_SectionNum&"."&Sub3_SectionNum&"."&Sub4_SectionNum&"."&Sub5_SectionNum&"."&Sub6_SectionNum
y = SectionNum,Sub1_SectionNum,Sub2_SectionNum,Sub3_SectionNum,Sub4_SectionNum,Sub5_SectionNum,Sub6_SectionNum
z = number of section/subsection levels, in this case 7
Formula:
  =ModuleNum&"."&LEFT(x,LEN(x)+COUNT(y)-z)

But I think your "for example" formula was actually really close to working, being correct, and better than my first instinct. Here it is fixed:

=ModuleNum&IF(COUNTA(SectionNum),"."&SectionNum,"")&IF(COUNTA(Sub1_SectionNum),"."&Sub1_SectionNum,"")&IF(COUNTA(Sub2_SectionNum),"."&Sub2_SectionNum,"")&IF(COUNTA(Sub3_SectionNum),"."&Sub3_SectionNum,"")&IF(COUNTA(Sub4_SectionNum),"."&Sub4_SectionNum,"")&IF(COUNTA(Sub5_SectionNum),"."&Sub5_SectionNum,"")&IF(COUNTA(Sub6_SectionNum),"."&Sub6_SectionNum,"")

Both of these assume that all columns you want to leave out will be blank. COUNTA will allow text and special characters; use COUNT if you only want numbers. If they're number columns, then it doesn't matter.

Here is the new formula that solves your problem with the following additional requirements: Any sub section numbers will be ignored if they are zero or are after a sub section with zero. SectionNum can be zero. Note that SharePoint only allows 8 levels of nesting, so you could add a Sub7_SectionNum but then not a Sub8_SectionNum.

=ModuleNum&IF(COUNTA(SectionNum),"."&SectionNum&IF(Sub1_SectionNum>0,"."&Sub1_SectionNum&IF(Sub2_SectionNum>0,"."&Sub2_SectionNum&IF(Sub3_SectionNum>0,"."&Sub3_SectionNum&IF(Sub4_SectionNum>0,"."&Sub4_SectionNum&IF(Sub5_SectionNum>0,"."&Sub5_SectionNum&IF(Sub6_SectionNum>0,"."&Sub6_SectionNum,""),""),""),""),""),""),"")
  • I haven't tried your first instinct, but after evaluating your second, I can say that it is not quite what I am looking for. The formula should stop evaluating when it reaches a 0, and the second formula does not do that. I wish there was a way I could post an attachment so I could show you my xlsx of what I mean. – jg100309 Oct 16 '13 at 13:22
  • Could you clarify what you mean about "stop evaluating when it reaches a 0"? So if ModuleNum = 10, SectionNum = 0 and Sub1_SectionNum is 5, you want if to display 10.0? You could use Dropbox or SkyDrive or Mega to host your example xlsx. – Aron Foster Oct 16 '13 at 13:32
  • Aron, There wouldn't be a 10.0.5. These are chapter numbers that we call Modules... If that helps how you think of things. Anyways, here's a link to my spreadsheet, let me know if there are issues accessing it. docs.google.com/file/d/0B6Zl61x54NhySTNyZ0dQazZsOGM/… – jg100309 Oct 16 '13 at 13:58
0

So @Aron Foster and I took this off-stack, and I wanted to share what the solution that he ended up coming up with to solve my issue.

=ModuleNum& IF(COUNTA(SectionNum),"."&SectionNum& IF(Sub1_SectionNum>0,"."&Sub1_SectionNum&IF(Sub2_SectionNum>0,"."&Sub2_SectionNum& IF(Sub3_SectionNum>0,"."&Sub3_SectionNum& IF(Sub4_SectionNum>0,"."&Sub4_SectionNum& IF(Sub5_SectionNum>0,"."&Sub5_SectionNum& IF(Sub6_SectionNum>0,"."&Sub6_SectionNum,""),""),""),""),""),""),"")

*Important to note: There is a maximum of 8 IF statements that can be used

Many Thanks @Aron Foster!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.