none
Using IF statement with AND in a calculated field someone please help

    คำถาม

  • Evening all, I hope to not be to long winded but I also hope to provide enough info so that someone smater than myself can help with this as it's killing me lol

    Cols:
    Fiscal Year
    Continuation (yes/no dropdown)
    PassedNumber (number field)

    Outcome 1:
    if continuation=No AND PassedNumber <=9
    (Result needed)Fiscal Year - Fiscal Year PassedNumber (12-1203)

    Outcome 2:
    if continuation=No AND PassedNumber >= 10
    (Result needed)Fiscal Year - Fiscal Year Passed Number (12-1210)

    Outcome 3:
    if continuation=Yes AND passedNumber <=9
    (Result needed)Fiscal Year PassedNumber "CONT" Fiscal Year (12-03CONT12)

    Outcome 4:
    if continuation = Yes AND passedNumber =>10
    (Result needed)Fiscal Year PassedNumber "CONT" Fiscal Year (12-10CONT12)

    Basically my issue stems from the fact the number my client is trying to produce needs to have a "0" when it less than 9. Sorry 1,2,3 doesn't float thier boat they want 01,02,03 etc.. but the number field (passedNumber) is from an automatic counter that drops the proceeding zero (similiar to Excel). in my statements I had to add the "0" to trick it into at least pretending to have a zero there

    This is what I have worked up so far but it's not working as intended. I created in Excel and attempted to paste it into the calculated field formula box to no advil. Please any help would be greatly appreciated or if there is an even better solution it would be great!!

    =IF(AND(Continuation="No",PassedNumber<=9),CONCATENATE(Fiscal_Year,"-",Fiscal_Year,"0",PassedNumber),IF(AND(Continuation="No",PassedNumber>=10),CONCATENATE(Fiscal_Year,"-",Fiscal_Year,PassedNumber),IF(AND(Continuation="Yes",PassedNumber<=9),CONCATENATE(Fiscal_Year,"-","0",PassedNumber,"CONT",Fiscal_Year),IF(AND(Continuation="Yes",PassedNumber>=10),CONCATENATE(Fiscal_Year,"-",PassedNumber,"CONT",Fiscal_Year)))))

    29 มิถุนายน 2555 0:41

คำตอบ

  • Parvez,

     Thanks for the reply and I did manage to replicate the desired results using your guidance however I also found another solution. I was able to use the VALUE function in the calculated field. Which changes numbers in text form back to actual numbers and then nest all the darn if/and statements

     Similiar to this:

    IF(AND(Continuation="No",VALUE( PassedNumber)<=9),CONCATENATE(Fiscal_Year,"-",Fiscal_Year,"0",PassedNumber),IF(AND(Continuation="No",VALUE(PassedNumber)>=10),CONCATENATE(Fiscal_Year,"-",Fiscal_Year,PassedNumber),IF(AND(Continuation="Yes",VALUE(PassedNumber)<=9),CONCATENATE(Fiscal_Year,"-","0",PassedNumber,"CONT",Fiscal_Year),IF(AND(Continuation="Yes",VALUE(PassedNumber)>=10),CONCATENATE(Fiscal_Year,"-",PassedNumber,"CONT",Fiscal_Year)))))

    • ทำเครื่องหมายเป็นคำตอบโดย LostInSharePoint2010 30 มิถุนายน 2555 16:37
    30 มิถุนายน 2555 16:37

ตอบทั้งหมด

  • Next time your client might say that they want to be able to perform calculation using the data that shows as "01, 02, 03", which isn't going to work because you're tricking them into appearing as "Text", which is what I would do too. I don't understand what's the big deal about having a "0" in front of it and I can go on and on about client's needs vs wants, but that's a different story.

    Try this. Use multiple calculated columns but keep them simple. I'm assuming that you're getting your desired results by having them come up as numbers (i.e. 1, 2, 3). Leave that as your main calculated result column. Next, create another calculated column that will use the previously calculated column's value and format the result as a text to show them as "01, 02, 03, etc." This way you can always modify the format of the result without affecting the calculation. In your views, you'll need to hide the column that does the calculation and only show the column that shows the result in certain format.

    Hope that helps...


    Pman
    http://www.pmansLab.com/

    30 มิถุนายน 2555 1:45
  • Parvez,

     Thanks for the reply and I did manage to replicate the desired results using your guidance however I also found another solution. I was able to use the VALUE function in the calculated field. Which changes numbers in text form back to actual numbers and then nest all the darn if/and statements

     Similiar to this:

    IF(AND(Continuation="No",VALUE( PassedNumber)<=9),CONCATENATE(Fiscal_Year,"-",Fiscal_Year,"0",PassedNumber),IF(AND(Continuation="No",VALUE(PassedNumber)>=10),CONCATENATE(Fiscal_Year,"-",Fiscal_Year,PassedNumber),IF(AND(Continuation="Yes",VALUE(PassedNumber)<=9),CONCATENATE(Fiscal_Year,"-","0",PassedNumber,"CONT",Fiscal_Year),IF(AND(Continuation="Yes",VALUE(PassedNumber)>=10),CONCATENATE(Fiscal_Year,"-",PassedNumber,"CONT",Fiscal_Year)))))

    • ทำเครื่องหมายเป็นคำตอบโดย LostInSharePoint2010 30 มิถุนายน 2555 16:37
    30 มิถุนายน 2555 16:37