Home

Need help with setting up custom function

%3CLINGO-SUB%20id%3D%22lingo-sub-725208%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20setting%20up%20custom%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725208%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20build%20a%20pricing%20sheet%20that%20can%20auto-calculate%20base%20cost%20of%20materials%20needed%20plus%20our%20services%20fees%20so%20we're%20not%20having%20to%20manually%20input%20on%20a%20calculator.%20Some%20materials%2Fline%20items%20will%20receive%20a%20percentage%20increase%20while%20others%20receive%20a%20dollar%20amount%20increase.%3C%2FP%3E%3CP%3ESee%20attached%20sheet%20for%20reference.%20I%20have%20the%20sheet%20set%20up%20currently%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20D%3A%20Base%20cost%3C%2FP%3E%3CP%3EColumn%20E%3A%20Base%20PLUS%20%25%20or%20%24%20increase%3C%2FP%3E%3CP%3EColumn%20F%3A%20Cost%20per%20unit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20function%20that%20can%20differentiate%20between%20the%202%20values%3F%20Something%20like%20%22IF%20value%20includes%20%22%25%22%20data%20%3D%20x%25%20OR%20IF%20value%20includes%20%22%24%22%20data%20%3D%20%24x.xx%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20above%20is%20not%20an%20available%20option%2C%20my%20secondary%20question%26nbsp%3B%3CSPAN%3Efor%20our%20percentage%20inputs%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20used%20both%26nbsp%3B%3C%2FSPAN%3E%3DE62*(1%2BF62)%20%26amp%3B%20%3D(D6%2BD6*E6)%20functions%20to%20get%20our%20answers.%20Both%20work%20well%2C%20but%20I'm%20not%20sure%20which%20is%20best%20for%20what%20I%20need%20or%20if%20both%20serve%20the%20purpose%20and%20either%20can%20be%20used%3F%20My%20main%20concern%20is%20when%20trying%20to%20calculate%20a%20percentage%20of%20a%20dollar%20value%2C%20it%20was%20showing%20increase%20in%20cents%20and%20not%20dollars%20(ie%3A%20%240.20%20for%2020%25%20of%20%24100%20vs%20the%20correct%20%2420.00)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-725208%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725290%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20setting%20up%20custom%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725290%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367323%22%20target%3D%22_blank%22%3E%40ajo5388%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20PLUS%20%25%20is%20always%20below%20100%25%20and%20your%20%24%20increase%20is%20always%20above%20%241.00%2C%20then%20you%20could%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(IF(E5%26lt%3B1%2CD5%2BD5*E5%2C0)%2CIF(E5%26gt%3B1%2CD5%3CSPAN%3E%2BE5%2C0))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725329%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20setting%20up%20custom%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725329%22%20slang%3D%22en-US%22%3EThis%20helps%20tremendously!%20BUT%20(sorry%20lol)%20Are%20you%20able%20to%20have%20it%20make%20the%20dollar%20amounts%20rounded%20up%20to%20the%20nearest%20dollar%20as%20well%3F%20I%20tried%20%3DROUNDUP(SUM(IF(E23%26lt%3B1%2CD23%2BD23*E23%2C0)%2CIF(E23%26gt%3B1%2CD23%2BE23%2C0)))%20and%20it%20said%20I%20entered%20too%20few%20arguments..%20%23wompwomp%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725331%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20setting%20up%20custom%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725331%22%20slang%3D%22en-US%22%3EI%20actually%20noticed%20my%20formula%20was%20unnecessarily%20long%2C%20try%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(E5%26lt%3B1%2CD5%2BD5*E5%2CROUNDUP(D5%2BE5%2C0))%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725345%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20setting%20up%20custom%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725345%22%20slang%3D%22en-US%22%3Elol%20SERIOUS%20LIFESAVER!!!%20Thank%20you%20SO%20SO%20much!%3C%2FLINGO-BODY%3E
ajo5388
New Contributor

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

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 

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
I actually noticed my formula was unnecessarily long, try this:

=IF(E5<1,D5+D5*E5,ROUNDUP(D5+E5,0))
lol SERIOUS LIFESAVER!!! Thank you SO SO much!
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies