Highlighted
New Contributor

# Need help with setting up custom function

Trying to build a pricing sheet that can auto-calculate base cost of materials needed plus our services fees so we're not having to manually input on a calculator. Some materials/line items will receive a percentage increase while others receive a dollar amount increase.

See attached sheet for reference. I have the sheet set up currently as:

Column D: Base cost

Column E: Base PLUS % or \$ increase

Column F: Cost per unit

Is there a function that can differentiate between the 2 values? Something like "IF value includes "%" data = x% OR IF value includes "\$" data = \$x.xx"

If the above is not an available option, my secondary question for our percentage inputs:

I have used both =E62*(1+F62) & =(D6+D6*E6) functions to get our answers. Both work well, but I'm not sure which is best for what I need or if both serve the purpose and either can be used? My main concern is when trying to calculate a percentage of a dollar value, it was showing increase in cents and not dollars (ie: \$0.20 for 20% of \$100 vs the correct \$20.00)

THANK YOU!!

4 Replies

# Re: Need help with setting up custom function

Hello @ajo5388,

Assuming your PLUS % is always below 100% and your \$ increase is always above \$1.00, then you could use this formula:

=SUM(IF(E5<1,D5+D5*E5,0),IF(E5>1,D5+E5,0))

Hope this helps!

PReagan

Highlighted

# Re: Need help with setting up custom function

This helps tremendously! BUT (sorry lol) Are you able to have it make the dollar amounts rounded up to the nearest dollar as well? I tried =ROUNDUP(SUM(IF(E23<1,D23+D23*E23,0),IF(E23>1,D23+E23,0))) and it said I entered too few arguments.. #wompwomp
Highlighted

# Re: Need help with setting up custom function

I actually noticed my formula was unnecessarily long, try this:

=IF(E5<1,D5+D5*E5,ROUNDUP(D5+E5,0))
Highlighted

# Re: Need help with setting up custom function

lol SERIOUS LIFESAVER!!! Thank you SO SO much!