Forum Discussion

PoojaManickam's avatar
PoojaManickam
Qrew Member
6 years ago

Formula

hi,
i'm trying to create a formula which will calculate a task completion factor depending other four factor like
A - Occurence
b- Time (duration required to complete the task),
C- cost 
D-Quality 
these are multiple choice field and i have create sub field for calculation 
and Field A has  (Very Low,Low,Medium,High,Very High ) and its sub field calculation 
If([Probability Of Occurrence]="Very Low (1-9%)","1",If([Probability Of Occurrence]="Low (10-29%)","2",If([Probability Of Occurrence]="Medium (30-50%)","3",If([Probability Of Occurrence]="High (51-70%)","4",If([Probability Of Occurrence]="Very High (71-99%)","5")))))

If([Time Impact]="Nil (0)","0",If([Time Impact]="Very Low (<1 mth)","1",If([Time Impact]="Low (1-2 mths)","2",If([Time Impact]="Medium (2-4 mths)","3",If([Time Impact]="High (4-6 mths)","4",If([Time Impact]="Very High (>6 mths)","5"))))))

now i would like to perform some operation the  A*(A+B) like 2*(1+3) =8 then we can categorize the task "easy,tough,moderate")

but im unable to perform above operation please assist

------------------------------
Pooja Manickam
------------------------------
Formulas and functions
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Pooja,

    Is your first field a Formula Text?  It appears you are declaring the values to be a text and not a numeric.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Pooja

    Like Don says, you need to change your sub fields to be type formula numeric l and then change the formula to take the numerical values out of quotes.  Also unlike Excel it is not necessary to nest your IFs.  Lastly when writing an IF formula  I suggest a vertical appearance for better readability

    If(
    [Probability Of Occurrence]="Very Low (1-9%)",1,
    [Probability Of Occurrence]="Low (10-29%)",2,
    [Probability Of Occurrence]="Medium (30-50%)",3,
    [Probability Of Occurrence]="High (51-70%)",4,
    [Probability Of Occurrence]="Very High (71-99%)",5)


    Then for your final field you can have the formula similar to your post

    [Probability] * ([Probability + [Cost])

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • PoojaManickam's avatar
      PoojaManickam
      Qrew Member
      6 years ago
      thanks mark and Don
      this above formula worked 
      once again many thanks for your support

      ------------------------------
      Pooja Manickam
      ------------------------------