How Do I Count Only Continuous 0's In A Column?

JohnStevensInc

Board Regular
Joined
Dec 2, 2003
Messages
109
I have three rows of data. M = Month. TP = Total Payment. IP = Interest Payment and PP = Principal Payment.

M TP IP PP
1 600 100 500
2 0 0 0
3 0 0 0
4 1800 300 1500
5 0 0 0
6 1200 200 1000


Interest of $100 is always deducted from the total payment. If a customer skips a month(s), then the previous month(s) Interest Payment and this month's Interest Payment must be deducted from the Total Payment prior to determining the Principal Payment. What is a formula I could use in the Interest Payment column to calculate as:

IF the previous month's Interest Payment was 0, then determine the number of previous month's Interest Payments that were 0 and times that number by the default interest payment ($100) and deduct that from the Total Payment to determine the Principal Payment. IF the previous month's Interest Payment was not 0 then deduct the default interest payment from the Total Payment to determine the Principal Payment.

Thank you. Any help is very much appreciated!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
How do the amounts for TP and PP get entered?

Perhaps you just need something in the IP column like :-

<TABLE style="WIDTH: 69pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=92><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 69pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=17 width=92>=B2/B$2*100</TD></TR></TBODY></TABLE>
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top