Multiple Conditions / Formulas in Single Cell

[breiter]

Sorry if this was asked before I simply have no idea what this is called or where to begin. I figure someone may be able to tell me this pretty quick without me spending hours getting nowhere.

Here is the what I am trying to figure out...

If you open a new excel spreadsheet type in the following cells.
B1... 500
A2... 700
C2... =A2+B2
B3... =B1-B2

I am trying to figure out how to deal with B2. I want it to update automatically according to these conditions...

• If A2 is less than 1,000 I want B2 to make up the difference based on what is available in B1. (In my example I want B2 to read 300, since A2 is 700, and therefore 200 leftover in B3).
• On the other hand if you change B1 to 100 and A2 remains less than 1,000 at 700, I want B2 to apply the 100 towards getting to the total of 1,000 (which in C2 would read 800 in this case).
• If A2 is 1,000 or higher I want B2 to read 0.00.

Thanks for any help!!

 

[gnrboyd]

If I understood your post, A2 is your starting amount, B1 is your available, B3 is your remainder after adding to A2 (if required) and B3 is what is left of your available.

If that is correct, this should work.

Book1
	A	B	C	D
1		500
2	700	300	1000
3		200
Sheet1

With different figures:

Book1
	A	B	C	D
1		100
2	700	100	800
3		0
Sheet1

 

[breiter]

This is exactly what I was looking for. Thanks so much!

 

[gnrboyd]

No problem. Glad it worked for you.