Forum Discussion

RoxanneZiegler's avatar
RoxanneZiegler
Qrew Cadet
2 months ago

Solved - Help with a formula

I am trying to build a formula and it's coming out with the wrong numbers.

I am trying to replicate an excel spreadsheet. The formula they have is

Total for this 8111.59

I have a numerical field called Purchase Price: example ($22,556.03)

I have a numerical currency field called Closing cost : ($1950)

I have a currency formula field called Closing Cost – 400/2   ([Closing Cost]-400): ($750)

This is the field I am having trouble with field Closing -400/2-IVA

Closing Cost -400/2/1.16  ( this number should be 667.10) it comes out at 4843.75

Last field is Currency formula

[Purchase Price]*0.33+[Closing Cost minus 400/2]-[Closing -400/2-IVA] This field should have a sum of 8111.59

It would be nice if I could do this all in one formula field but I am not great at formulas so I thought breaking it down step by step would work – so that the person using it in reports would be able to see step by step

 I am lost and I am hoping someone can help me

thanks a bunch - hope that made sense 

 

 

 

formulas
  • Mez's avatar
    Mez
    Qrew Cadet
    2 months ago

    I think you're on the right track to have each piece of data sit in its own field; makes the formula easier to work with and debug later. 

    It looks like your formula is off for Closing Cost - 400 / 2 since you've indicated this value should be *750. My guess is you need to first subtract the 400 from closing cost, then divide by 2.

    *([Closing Cost]-400) / 2 = 775 - this value is also what shows in the screenshot for the formula. If this should be 750 then one more in put is needed. 

    Since you already have a formula field doing this calculation, you only need to then refer to this field in your IVA field: [Closing Cost - 400 / 2] * 1.116 should then give you 668.10; even though you've noted 667.10. We are getting closer. 

    If this is true, then once you fix this your formula, all calculations should align. 

    • RoxanneZiegler's avatar
      RoxanneZiegler
      Qrew Cadet
      2 months ago

      What I did was made a separate Numerical field called Closing Tax- and I also separated the percentage for commission for Company - it was Purchase Price *33 but I decided in case that number changes to make a separate field  

      I had to split every thing up – as you can see until I add everything up the numbers are correct

      Closing- 775.00

      Closing/IVA -668.10

      Percentage .33 – 7443.49

       

      Should add up right

       

      Then I take all those and add them up in Company Comm Total [Commission For Company]+[Closing Cost minus 400/2]+[Closing -400/2-IVA]

      And it pukes - it's like it does not like all those formulas in one or do I need some kind of [ }  on there? 

       

       

       

       

       

      • Mez's avatar
        Mez
        Qrew Cadet
        2 months ago

        Should it be Commission or Purchase price?

         "add them up in Company Comm Total [Commission For Company]+[Closing Cost minus 400/2]+[Closing -400/2-IVA]"

        Seeing your fields, this is how I would construct: 

        ( [Purchase price for reports] * [Percentage for company] ) + ( [Closing cost minus 400/2] / [IVA of closing] )

        (22,556.03 * 0.33) + (775 / 1.160) = 8,111.59

        Q: Percentage for company is showing as currency - is this truly what you want?

  • RoxanneZiegler's avatar
    RoxanneZiegler
    Qrew Cadet
    2 months ago

    I took the advice and changed the company percent from currency to numeric percent

     

    But before that I finally figured out what was the problem - I was actually trying to subtract that 775 again.

     

    thanks for everyone's input - its solved