If Statement with Sum

C

Chip1035

I did not know how to search for this, so my apologies if it has been posted
before.
A1 | B1 | C1 | D1
$20 $35 $5 $5
I have in A1, $20 allowed, B1 has $35 used, C1 is $5 left over from previous
month
What I want D1 to have is the amount over what was allowed in A1 but not to
exceed what is in C1. Also, if B1 does not exceed A1, then D1 should be
blank.
So far I came up with this
IF(G6>E6,G6-E6,"") which would get me 15, which is over C1. If it is over
C1, I want it to equal C1. I tried this:
IF(B1>A1,(SUM(B1-A1)>C1,C1),"") but got an #VALUE! error.
Thank you for any and all assistance, in advance.
 
K

kassie

I don't understand what you want to achieve here? After all, 20 - 35 = -15!
However, try the following
=IF(AND(B1>A1,B1-A1>C1),C1,IF(B1>A1,B1-A1,IF(B1<A1,0,"")))
 
D

David Biddulph

Chip1035 said:
I did not know how to search for this, so my apologies if it has been
posted
before.
A1 | B1 | C1 | D1
$20 $35 $5 $5
I have in A1, $20 allowed, B1 has $35 used, C1 is $5 left over from
previous
month
What I want D1 to have is the amount over what was allowed in A1 but not
to
exceed what is in C1. Also, if B1 does not exceed A1, then D1 should be
blank.
So far I came up with this
IF(G6>E6,G6-E6,"") which would get me 15, which is over C1. If it is over
C1, I want it to equal C1. I tried this:
IF(B1>A1,(SUM(B1-A1)>C1,C1),"") but got an #VALUE! error.
Thank you for any and all assistance, in advance.
Click to expand...

=IF(B1>A1,MIN((B1-A1),C1),"")
 
Top