Need help with setting up custom function

[ajo5388]

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!!

[#2]

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 

[#3]

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

[#4]

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

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

[#5]

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