formula help

[Steve T]

cell J2 has starting balance of 500,000
cell J3 has running sum from column D
column D has both positive and negative entries
The running sum in cell J3 is 25% of total entries in column D
I need to subtract cell J3 from cell J2 but when I enter too many negative
numbers
the initial amount goes past 500,000 in J2.
How do I keep cell J2 from going past the initial 500,000?
Excel 97 SR-1
Thanks, Steve T

 

[Anne Troy]

If you're saying you don't want it to SHOW less than 500,000, then you could
use something like:
=IF(MYFORMULA<500,000,500,000,MYFORMULA)
If you want to be ALERTED when it's less than 500,000, then use conditional
formatting, perhaps, to give the cell a red fill. Format-->Conditional
formatting...
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[JE McGimpsey]

How is cell J2 changing?


It might help to give the exact formulas you're using in J2 and J3.

 

[Steve T]

Sent reply to JE only by mistake sorry. Here was my reply:
J2 cell formula is =SUM(500000-J3)
J3 cell formula is =SUMIF(D10402,">0")
I'm looking to eliminate the amount of 500000 at the rate of applying 25%
to the sum in column D in J3. Currently there are two negative amounts
totaling 140 and one positive amount for 35. Only the 35 is subtracted from
the 500000 in J2. It should be a negative amount. The 25% should only be
applied if it appears as a "profit". Steve T

 

[anon]

Sent reply to JE only by mistake sorry. Here was my reply:
J2 cell formula is =SUM(500000-J3)
J3 cell formula is =SUMIF(D10402,">0")
I'm looking to eliminate the amount of 500000 at the rate of applying 25%
to the sum in column D in J3. Currently there are two negative amounts
totaling 140 and one positive amount for 35. Only the 35 is subtracted from
the 500000 in J2. It should be a negative amount. The 25% should only be
applied if it appears as a "profit". Steve T

In cell J2 try this:

=max(0,500000-(J3*.25))

Jim

 

[JE McGimpsey]

I guess I'm confused as to what you're trying to accomplish.

J3 will always be zero or positive since the SUMIF() only sums positive
numbers.

J2 will always be less than or equal to 500000 since you're subtracting
a zero or positive value from 500000. (Note that the SUM() is not
needed, you could use

=500000 - J3

without the overhead of the SUM function.)

So I don't know what "It" you mean when you say that "It should be a
negative amount".

Note that 35 is not 25% of -140 (if anything it's -25%, but language
gets tricky when talking about %'s and sign changes).

If you want to J3 to be 25% of the total of column D, but only if the
column D sum is positive, then it sounds like you want

J3: =MAX(0, SUM(D) * 25%)

 

[Steve T]

Crap I did it again by not replying to group, sorry.
I'm trying to figure out a payment schedule of 25% of profits with a
starting balance of 500000. If there is no "profit" then there is no 25%
payment. The numbers will be entered daily, negative or positive; and if at
the end of the month there is a profit, then you owe me 25% of that profit
until the 500000 is paid off. If you do not make a profit for the month, you
owe me nothing; but the original 500000 does not increase. I'm trying to
make the worksheet capable of daily entries with running totals. That's
about as "simple" as I can make it. Thanks for all of the effort, Steve T.

 

[Roger Govier]

Hi Steve

Is it not
J2=500,000-J3
J3=IF(SUM(D10402)>0,SUM(D10402)*25%,0)

 

[Steve T]

Thanks Roger, that formula keeps it at 500000 id there is no profit to pay.
Steve T.