SOLVED
Home

Help I want the result of a formula to have a minimum value

%3CLINGO-SUB%20id%3D%22lingo-sub-905391%22%20slang%3D%22en-US%22%3EHelp%20I%20want%20the%20result%20of%20a%20formula%20to%20have%20a%20minimum%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905391%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20preparing%20a%20spreadsheet%20with%20some%20formulas%20where%20it%20calculates%20a%20cost%20to%20charge%20a%20customer.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EUnits%20of%3C%2FTD%3E%3CTD%3ECalculated%20Cost%3C%2FTD%3E%3CTD%3EAmount%20to%20be%20Charged%20(Min%20%C2%A3200)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%C2%A3230%3C%2FTD%3E%3CTD%3EFALSE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20'Calculated%20Cost'%20currently%20read%20as%20follows%3B%26nbsp%3B%3DSUM(A2*50)%2B30%26nbsp%3B%20%26nbsp%3BThis%20works%20perfectly%20as%20required.%20(%C2%A330%20is%20an%20admin%20fee).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20be%20able%20to%20do%20is%20to%20either%20modify%20this%20formula%20so%20that%20if%20the%20'Units%20of'%20was%20for%20example%203%2C%20the%20result%20would%20be%20%C2%A3180%20but%20I%20want%20the%20amount%20to%20be%20charged%20to%20be%20%C2%A3200%20(as%20a%20minimum%20charge.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%2C%20in%20this%20scenario%2C%20%C2%A3200%20would%20automatically%20appear%20although%20as%20you'll%20see%2C%20I'd%20be%20happy%20to%20have%20the%20'Amount%20to%20be%20Charged%20(Min%20%C2%A3200)'%20in%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnybody%20know%20the%20exact%20formula%20to%20get%20this%20desired%20result%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-905391%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-905502%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20I%20want%20the%20result%20of%20a%20formula%20to%20have%20a%20minimum%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423681%22%20target%3D%22_blank%22%3E%40Paulus100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%20in%20B2%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMAX(A2*50%2B30%2C200)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-905634%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20I%20want%20the%20result%20of%20a%20formula%20to%20have%20a%20minimum%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905634%22%20slang%3D%22en-US%22%3EFantastic%2C%20thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-905650%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20I%20want%20the%20result%20of%20a%20formula%20to%20have%20a%20minimum%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-905650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423681%22%20target%3D%22_blank%22%3E%40Paulus100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20pleasure%20mine!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Paulus100
New Contributor

I'm preparing a spreadsheet with some formulas where it calculates a cost to charge a customer.

Units ofCalculated CostAmount to be Charged (Min £200)
4£230FALSE

 

The formula in 'Calculated Cost' currently read as follows; =SUM(A2*50)+30   This works perfectly as required. (£30 is an admin fee).

 

What I want to be able to do is to either modify this formula so that if the 'Units of' was for example 3, the result would be £180 but I want the amount to be charged to be £200 (as a minimum charge.

 

Ideally, in this scenario, £200 would automatically appear although as you'll see, I'd be happy to have the 'Amount to be Charged (Min £200)' in another cell.

 

Anybody know the exact formula to get this desired result?

 

Thank you in advance,

 

Paul

3 Replies
Solution

@Paulus100 

Try this formula in B2: 

=MAX(A2*50+30,200)

Fantastic, thank you so much!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies