Discussions

 View Only
  • 1.  Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 13:59
      |   view attached
    I have attached a photo of a formula I have been working on. A bit of background information: I will use this to create an estimate for clients for some of our products. This formula field will display our fee based on the total cost the the Estimate Cost field. The first few statements are based on cost with a flat fee associated. After $3000.00 the costs will be based on percentage. It worked until I added the percentage portion. I've written it a few times, and after so many times of staring at it I can't seem to get it right. Please help :)

    ------------------------------
    Anna Harris
    ------------------------------


  • 2.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:15
    Best bet to get quicker help is to post the actual formula as text so people can copy and paste it. Also is this a text field? I am guessing text as the numbers have quotes around them but it would be good to know for sure.

    With an offhand guess I would wrap the yellow text in a ToText function.


  • 3.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:16
    Formula:

    If([Estimate Cost]<=500,"100.00",[Estimate Cost]>=500,"150.00",[Estimate Cost]>=1000, "250.00",[Estimate Cost]>=3000,[Estimate Cost] *.12)

    ------------------------------
    Anna Harris
    ------------------------------



  • 4.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:23
    Try this Anna:
    If(
    [Estimate Cost]=0,null,
    [Estimate Cost]<=500,100,
    [Estimate Cost]>500 and [Estimate Cost]<=1000,150,
    [Estimate Cost]>1000 and [Estimate Cost]<=3000,250,
    [Estimate Cost]*0.12)​


    [Estimate Cost] needs to be numeric and the formula field needs to be formula numeric.



    ------------------------------
    Adam Keever
    ------------------------------



  • 5.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:29
    That makes sense until the percentage. So as a breakdown: 
    My fee is as follows: 

    499.99 and under fee is 100.00
    500.00 - 999.99 fee is 150.00
    1000.00 - 2999.99 fee is 250.00
    3000.00 + fees will be 12 %  (ie: 3000.00  fee is 360.00 and so on...) 

    But the way this is written looks like it will add 12% to everything?  or will this only add 12% to anything that does not fit in the prior statements??

    ------------------------------
    Anna Harris
    ------------------------------



  • 6.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:32
    Try this then. The last part of the if statement, [Estimate Cost]*0.12, covers anything that is equal or greater than 3000.

    If(
    [Estimate Cost]=0,null,
    [Estimate Cost]<500,100,
    [Estimate Cost]>=500 and [Estimate Cost]<1000,150,
    [Estimate Cost]>=1000 and [Estimate Cost]<3000,250,
    [Estimate Cost]*0.12)​​


    ------------------------------
    Adam Keever
    ------------------------------



  • 7.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:35






    ------------------------------
    Adam Keever
    ------------------------------



  • 8.  RE: Formula - Total field if/then based on numeric value and percent.

    Posted 10-24-2019 14:36
    Today your a hero Adam!!! Thanks for your help! It worked perfect!

    ------------------------------
    Anna Harris
    ------------------------------