need formula to show positive withdrawal as "0"

[hmr2662]

I am finishing up a retirement calculator and have the situation where my formula is computing how much of the 401K withdrawal to subtract from the beginning 401k balance the next year. As an example, my beginning 401K balance in year 2 = end 401K balance year 1 minus 401k withdrawal in year 1. This works fine for years when there is an actual withdrawal. I am showing my withdrawals as (negative numbers) and "adding" that column back as described.

The problem is that in years where annual income exceeds retirement withdrawal needed, it computes a POSITIVE withdrawal. I do not know how to adjust the formula to add another condition so that when r21>0, it should not be added.

column g= end 401k balance
column r = 401k withdrawal
yr1= line 21

beg 401k balance yr2(line 22)=IF(ISNUMBER(R21),(G21+R21),G21)

Thanks

Screenshot attached.
HR

[martindwilson]

any formula you have if you want to show -ve or 0 if positive
wrap it in min()
=min(your formula,0) so it will only show -ves or zero

[hmr2662]

That did not work. I used this formula in the 401k withdrawal column (column R) as you suggested:

=MIN(IF(ISNUMBER(T21),(T21*0.75),0))

It is still displaying withdrawals that are positive numbers. Did I do something wrong?
HR

[hmr2662]

Re: Formula Which Will Only Show A Positive Profit And Remain Blank When Negative Profit


I found this from a similar post below and it may work but I do not know how to write the conditional formula so it evaluates where it will only show the calculated value when (formula)<0. Help please: I get confused with where to put all the parentheses!

IF((ISNUMBER(T21),(T21*0.75),0)))<0,t21*0.75,0) ??????

If you have an existing formula that does the calculation, you can plug it in here

=IF(profit>0,profit,"")

Just replace both occurances of profit with your existing calculation

[martindwilson]

=min(if(isnumber(t21),(t21*0.75),0),0)

[hmr2662]

Martin,

Thanks, that worked! I also toyed around with another formula that worked as well. I will update post as solved. That is good to know about the syntax for min with an isnumber function. This is very useful in excel.
=IF(AND(ISNUMBER(T21),T21<0),(T21*0.75),"")
HR

[hmr2662]

Martin,
I am trying to add to your reputation as thanks but not sure how to do it.
HR