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!

Improve this question
2
  • 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
    Commented Oct 15, 2013 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
    Commented Oct 16, 2013 at 12:36

2 Answers 2

Reset to default
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,""),""),""),""),""),""),"")
Improve this answer
3
  • 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
    Commented Oct 16, 2013 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
    Commented Oct 16, 2013 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
    Commented Oct 16, 2013 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!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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