Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-3118930%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3118930%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20that%20will%20calculate%20a%20number%20in%20a%20specific%20cell%20up%20to%20%24350.00.%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20I%20enter%20%24230.00%20into%20cell%20A1%2C%20I%20want%20B1%20to%20calculate%20the%20difference%20up%20to%20350%24.%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20I%20want%20it%20to%20automatically%20figure%20out%20the%20difference%20is%20%24120.00%20and%20B1%20will%20say%20%24350.00%20when%20I%20enter%20%24230.00%20into%20A1.%3C%2FP%3E%3CP%3EIf%20nothing%20is%20in%20cell%20A1%2C%20I%20want%20B1%20to%20be%200.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3118930%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-3119006%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1297557%22%20target%3D%22_blank%22%3E%40tascott75%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20B1%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(A1%3D%22%22%2C%200%2C%20MAX(0%2C%20350-A1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3119190%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119190%22%20slang%3D%22en-US%22%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E!%20I%20tried%20the%20formula%20however%20it's%20putting%20into%20B1%2C%20the%20difference%20between%20A1%20and%20B1.%3CBR%20%2F%3EIf%20I%20put%2069.75%20into%20A1%2C%20B1%20will%20show%20280.25%2C%20instead%20of%20350.00.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3119231%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119231%22%20slang%3D%22en-US%22%3EYes%2C%20you%20said%20you%20want%20B1%20to%20show%20the%20difference%20between%20A1%20and%20350!%3CBR%20%2F%3EWhat%20exactly%20you%20want%3F%3C%2FLINGO-BODY%3E
New Contributor

I need a formula that will calculate a number in a specific cell up to $350.00.

For example, if I enter $230.00 into cell A1, I want B1 to calculate the difference up to 350$.

In other words, I want it to automatically figure out the difference is $120.00 and B1 will say $350.00 when I enter $230.00 into A1.

If nothing is in cell A1, I want B1 to be 0.

Thanks.

7 Replies

@tascott75 

 

In B1:

 

=IF(A1="", 0, MAX(0, 350-A1))

 

 

Thank you @Joe User! I tried the formula however it's putting into B1, the difference between A1 and B1.
If I put 69.75 into A1, B1 will show 280.25, instead of 350.00.
Yes, you said you want B1 to show the difference between A1 and 350!
What exactly you want?
My apologies that I wasn't clear.
I feel like there isn't actually a formula to calculate what I am looking for. Thank you for your help though!

 

@tascott75  wrote:  ``My apologies that I wasn't clear.  I feel like there isn't actually a formula to calculate what I am looking for.``

 

 

Don't give up on us so quickly.

 

Originally, you wrote:  ``[the formula will] figure out the difference is $120.00 and B1 will say $350.00 when I enter $230.00 into A1.``

 

So, it is indeed clear that B1 is __not__ the difference between A1 and 350.  My bad!

 

You provided an example where A1 is less than 350.  What should B1 return if A1 exceeds 350?

 

Perhaps you want the following in B1:

 

=IF(A1="", "", MAX(350, A1))

@Joe User Well, you have proven me wrong! That was exactly the right formula and what I needed to show in the cell! (And A1 would never exceed 350 for my purposes.) I really appreciate the help, I'm sorry for giving up so quickly. Thanks so much, problem solved!

@tascott75  wrote: ``And A1 would never exceed 350 for my purposes.``

 

Huh?!  Then perhaps all you need in B1 is:

 

=IF(A1="", "", 350)

 

In my previous response, MAX(350, A1) returns 350 whenever A1 is less, and it returns A1 whenever it is more than 350.  But if A1 "never exceeds 350", MAX(350, A1) always returns 350. (!)