Hi there! I am just getting my feet wet with formulas, and need some guidance on two points:
How do I remove the cents from dollars in my formula? Essentially, I only want whole dollars displayed.
Is there a way for me to round up to the nearest $5 increment, and what would that look like? I am able to round to the nearest dollar, but we would like to have these to the nearest $5 increment.
{note}-------- CS Input Area - Not Displayed to Customer --------
Estimated Labor: {formtext: name=low end hours} - {formtext: name=high end hours} hours for {formtext: name=number of fixers} Fixer(s)
Labor Rate: ${formmenu: 119; default=129; 139; 159; name=Choose Rate}
Estimated Materials: ${formtext: name=estimated materials; default=0}
--------------------------------------------------------------------------------{endnote}
We estimate this job to cost around {=round(number of fixers(low end hours * choose rate))+estimated materials; format=$,.2f} - {=round(number of fixers(high end hours * choose rate))+estimated materials; format=$,.2f}.
Option 1 ★ Member Pricing: {=round(.85*(number of fixers(low end hours * choose rate)) + estimated materials); format=$,.2f} - {=round(.85(number of fixers*(high end hours * choose rate)) + estimated materials); format=$,.2f}
Care & Protect Members receive 15% off all Fixer labor costs, no charge for time spent shopping for materials, and many more benefits. Ask me how you can save or learn more at Home Maintenance Plan | Fixer Care & Protect!
To get the whole dollars to display, remove the ,.2f from your format= setting. You should leave it just as format=$ and that'll give you the dollar amount only.
For the rounding to the nearest $5... I can think of a way of doing this with an if statement. Before I create that example let me ask you how you want the rounding to work -- should it always round up? e.g. if it's $101, should that round to $105 or to $100? Same question for ending in 2, ending in 6, and ending in 7.
@Dano okay I got this working -- it turned out to be a pretty long set of IF statements because of the multiple different pricing options.
This is calculating the lowend, highend, memberlow, and memberhigh as their own variables.
Then we're looking at the very last digit of those variables and if it's not 0 or 5, we're rounding it up to 0 or 5.
NOTE: for your member price, I wasn't sure if the 85% rate applied to materials as well, so in my example below the 85% rate is only on labor and materials are still added at full price to the member totals.
{note}-------- CS Input Area - Not Displayed to Customer --------
Estimated Labor: {formtext: name=low end hours} - {formtext: name=high end hours} hours for {formtext: name=number of fixers} Fixer(s) Labor Rate: ${formmenu: 119; default=129; 139; 159; name=Choose Rate} Estimated Materials: ${formtext: name=estimated materials; default=0} {lowend=round(`number of fixers`*(`low end hours`* `choose rate`))+`estimated materials`; format=$; trim=yes}{memberlow=round((0.85*(`number of fixers`*(`low end hours` * `choose rate`)))+`estimated materials`); trim=yes} {highend=round(`number of fixers`*(`high end hours` * `choose rate`))+`estimated materials`; format=$; trim=yes}{memberhigh=round((0.85*(`number of fixers`*(`high end hours` * `choose rate`)))+`estimated materials`); trim=yes}{lastdigitlow=right(lowend, 1); trim=yes}{lastdigithigh=right(highend,1); trim=yes}{memberlowdigit=right(memberlow,1); trim=yes}{memberhighdigit=right(memberhigh,1); trim=left} --------------------------------------------------------------------------------{endnote} We estimate this job to cost around ${if: lastdigitlow==0 OR lastdigitlow==5}{=lowend}{elseif: lastdigitlow==1 OR lastdigitlow==6}{=lowend+4}{elseif: lastdigitlow==2 OR lastdigitlow==7}{=lowend+3}{elseif: lastdigitlow==3 OR lastdigitlow==8}{=lowend+2}{elseif: lastdigitlow==4 OR lastdigitlow==9}{=lowend+1}{endif} - ${if: lastdigithigh==0 OR lastdigithigh==5}{=highend}{elseif: lastdigithigh==1 OR lastdigithigh==6}{=highend+4}{elseif: lastdigithigh==2 OR lastdigithigh==7}{=highend+3}{elseif: lastdigithigh==3 OR lastdigithigh==8}{=highend+2}{elseif: lastdigithigh==4 OR lastdigithigh==9}{=highend+1}{endif}.
Option 1 ★ Member Pricing: ${if: memberlowdigit==0 OR memberlowdigit==5}{=memberlow}{elseif: memberlowdigit==1 OR memberlowdigit==6}{=memberlow+4}{elseif: memberlowdigit==2 OR memberlowdigit==7}{=memberlow+3}{elseif: memberlowdigit==3 OR memberlowdigit==8}{=memberlow+2}{elseif: memberlowdigit==4 OR memberlowdigit==9}{=memberlow+1}{endif}- ${if: memberhighdigit==0 OR memberhighdigit==5}{=memberhigh}{elseif: memberhighdigit==1 OR memberhighdigit==6}{=memberhigh+4}{elseif: memberhighdigit==2 OR memberhighdigit==7}{=memberhigh+3}{elseif: memberhighdigit==3 OR memberhighdigit==8}{=memberhigh+2}{elseif: memberhighdigit==4 OR memberhighdigit==9}{=memberhigh+1}{endif}. Care & Protect Members receive 15% off all Fixer labor costs, no charge for time spent shopping for materials, and many more benefits. Ask me how you can save or learn more at Home Maintenance Plan | Fixer Care & Protect!
I wouldn't be surprised if one of our engineers has a more elegant solution than what I have here, but this will definitely work for you