Jun 27 2019 07:31 AM
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!!
Jun 27 2019 08:08 AM - edited Jun 27 2019 08:13 AM
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
Jun 27 2019 08:22 AM
Jun 27 2019 08:25 AM
Jun 27 2019 08:32 AM