Formula to have a number in a column go to zero, if adjacent cell goes to zero

[BryanHCR]

I'm creating a debt reduction spreadsheet based on the Snowball debt reduction model. What I'm hoping to be able to do is have (the month number) in a cell (which is in the months it takes to pay off a debt column) go to zero, once the adjacent cell (in the balance owed column) goes to zero. Does anyone know a formula to do this? In my layman language it would be worded as: "When B10 is zero, then A10 displays zero".

I'm currently using the =MAX(A:A) formula to display the highest number in the months column (which is 120 because the spreadsheet months column goes to 120) in a cell that I will link to another form. However, this MAX formula is only useful in this case if the months number stops displaying once the debt of a particular creditor is paid. So, let's say it will take 24 months to pay a debt, then I want all months past 24 to go to zero so that the MAX formula displays 24.

I've included the spreadsheet so that anyone who can help can see in the spreadsheet what I'm talking about.

I've also included a screen pic with further details.

Thanks,
Bryan

PS This is the first time I have tried to add an attachment and the attachment button doesn't seem to be working properly. Perhaps someone can help me with that too. : )

[mehmetcik]

The Icon to attach a file does not work.

Click on the "go advanced" icon to open the advanced options and then select "Manage Attachments"

[BryanHCR]

Hi mehmetcik,

Thanks so much for responding. I added the attachments as best as I could see how. Not sure if they will show up when I post this, as nothing is showing up in this window. Bit of a learning curve for me here.

I liked your "rules for engagement" Say "Please & Thank you" "Make requests not demands" etc. I write about the importance of these ways of relating in my book, so they especially got my attention. And wow, over 6400 post for you, that's very impressive so I feel privileged that you responded to my request for help. Let's see how those attachment go! Thanks again!

I now see that the attachments worked! I tried to add a reputation point, clicked on the * and it "informed" me I had 10 reputation points, but I don't think it added any to you...

I took another look at my .jpg attachment, and need to note a correction. On the .jpg image I say Column "B" which is the months column, should stop displaying at 5, but that number should be 4, because that is the amount of time it will take to pay off that Creditor, which would then mean the MAX (B:B) formula would display the number "4" in D6.

[avk]

"d6"

Please Login or Register  to view this content.

[Bo_Ry]

Please try at D6
=COUNTIF(C$9:C$128,">0")+1

or B9 copy down
=IF(C8>0,ROWS(B$9:B9),"")

[BryanHCR]

Hi Bo_Ry,

Thanks so much for your response! This formula you gave =IF(C8>0,ROWS(B$9:B9),"") is the one that worked. Super helpful and has me smiling!

Thanks again!

PS Wish I could send some points or merits your way, but I can't see how to do that...

PSS I won't change status to solved quite yet, because I still need to do more testing, but I'm pretty sure you've given me what I need, and I'll post the final results sometime later today.

[FlameRetired]

@ BryanHCR

PS Wish I could send some points or merits your way, but I can't see how to do that...

See the star and "Add Reputation" in the lower left pane of Bo_Ry's post in question. Click that. You can also add optional comment.

[BryanHCR]

Thanks Dave, I couldn't see the Add Reputation until I clicked on a "bar" and the windows expanded. Yesterday the * wasn't working, but today it did.
Also, I'd like to set this thread to solved, but can no longer locate/see where to do that...

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.