7 Replies Latest reply on Oct 18, 2015 3:39 PM by Littleman1

    If & Then Calculation

    Littleman1 Level 1

      Hi Experts!!!

       

      I have a basic understanding but need some help. Creating a buget page and need som assistance as follows:

       

      Variable Inputs:

      Field 1: Expense Amount Entered ( Eg: $100.00 )

      Field 2: Dropdown box with the following:

                  1. " - "

                  2. " weekly "

                  3. " monthly "

                  4. " annually "

       

      Fixed Outcomes:

      Field 3: Will give the weekly expense

      Field 4: Will give the monthly expense

      Field 5: Will give the annual expense

       

      In summary, regardless of the frequency of the input ( weekly, monthly, annually ), the formula will calculate all three outcomes i.e weekly, monthly and annually.

       

      Thanks in advance guys,

       

      Nick

        • 1. Re: If & Then Calculation
          Test Screen Name Most Valuable Participant

          What is your calculation basis? That is if you were working it out on paper, what method would you use to go from weekly to monthly and weekly to annual? There are many different ways to do it, and you must use the method your accountant or finance office recommend.

          • 3. Re: If & Then Calculation
            Littleman1 Level 1

            The idea is:

             

            1. Amount Entered

            2. Frequesncy chosen ( weekly Monthly Annually ) from drop down list

            3. This figure is calculated to fill automatically the Weekly, Monthly & annual colums.

             

            The figures above in Weekly Monthly Annual columns have been manually entered so that you can see what I am trying to do.

            • 4. Re: If & Then Calculation
              Test Screen Name Most Valuable Participant

              Ok, so what is the actual formula used to make the lines of the table? Programming needs the exact formula, not just examples to work from. Do you have any JavaScript or programming knowledge or are you looking for us to point you at self-study materials from the very beginning?

              • 5. Re: If & Then Calculation
                Littleman1 Level 1

                The figures in the image were just manually input in the WEEKLY, MONTHLY and ANNUAL columns, so that I could show what I wanted to appear, based on the inputs in the AMOUNT & FREQUENCY COLUMNS.

                 

                My knowledge is limited but tried the following formula in the weekly column, which ofcourse does not work.

                 

                var v1 = this.getField("Amount").value;

                var v2 = this.getField("Frequency").value;

                if( v2 = "weekly" ) event.value = Amount;

                else if( v2 = "monthly" ) event.value = Amount * 12 / 52;

                else if( v2 = "annually" ) event.value = Amount / 52;

                else event.value = 0;

                 

                As I said, give me MS excel, I can write formaula's till hell freezes over but with Adobe, it another story.

                • 6. Re: If & Then Calculation
                  Test Screen Name Most Valuable Participant

                  Ok... the pull down thing could make it pretty complicated and send you round in circles, but your code looks quite plausible. I can however see an error that catches out everyone.

                   

                  To see if values are equal, never, never just use on equals sign = because it doesn't do what you think and breaks everything!

                  Use two equals signs if ( v2 == "monthly" ) . More info: JavaScript Comparison and Logical Operators

                   

                  Other problems: there are two lists of names of things. One is the name of the fields (Amount, Frequency) and one is the name of the variables (v1,v2). You connect them together with getField, but you can't ever use the field names in JavaScript apart from that. So notice that when you write Amount / 52 and so forth, you need to use the variable name, not the field name.

                  • 7. Re: If & Then Calculation
                    Littleman1 Level 1

                    Thanks for all your help. Formulas worked and worksheet is complete.