Need help with excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3075104%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3075104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3ERe%3A%26nbsp%3B%20Help%20with%20how%20to%20set%20If%20formula%20in%20excel%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20following%20spreadsheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EName%26nbsp%3B%20%26nbsp%3B%20Units%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCharge%20per%20unit%20%241%20up%20to%20500%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Charge%20per%20unit%2080c%20over%20500%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Total%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20560%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EMary%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20is%20if%20the%20value%20in%20the%20Units%20column%20is%20equal%20to%20or%20over%20%24500%20each%20unit%20is%20charged%20at%20%241%20and%20the%20balance%20over%20%24500%20is%20charged%20at%20%240.80%2C%20then%20I%20want%20to%20total%20the%20values%20so%20if%20someone%20has%20e.g.%20929%20units%20their%20total%20is%20%24842.20%20and%20if%20someone%20has%20e.g.%20460%20units%20their%20total%20is%20%24460%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20write%20this%20formula%20please%3F%3F%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3075104%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-3075215%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3075215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1289376%22%20target%3D%22_blank%22%3E%40Jemm27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20a%20number%20of%20units%20in%20B2.%20The%20total%20charge%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMIN(B2%2C500)%2B0.8*MAX(B2-500%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Re:  Help with how to set If formula in excel

 

Hi,

 

I have the following spreadsheet:

 

Name    Units     Charge per unit $1 up to 500      Charge per unit 80c over 500      Total

John        560                                                                  

Mary         20                                                                      

 

What I want to do is if the value in the Units column is equal to or over $500 each unit is charged at $1 and the balance over $500 is charged at $0.80, then I want to total the values so if someone has e.g. 929 units their total is $842.20 and if someone has e.g. 460 units their total is $460

 

How do I write this formula please??  Thank you!

 

2 Replies

@Jemm27 

Let's say you have a number of units in B2. The total charge is

 

=MIN(B2,500)+0.8*MAX(B2-500,0)

 

This can be filled down.

Thanks a million @Hans Vogelaar that solved my problem!