If Statement - Calculating quarterly payments %

Hello all,

Arrgh I am struggling here ( I really need some help if anyone is willing.

I am trying to calculate the payment %, of a cost realized on a quarterly basis.

Every 3rd month, the current month % + previous 2 month %'s, need to be added.

Please see example attached: Column of interest is column 17 (highlighted yellow).

However, if the time period is 7 months, I only want the 7th month to calculated the remaining %. I.e. 100% minus all of the previous % payments realized every 3 months.

e.g.
Period: 1=0%, 2=0%, 3=42%, 4=0%, 5=0%, 6=42%, 7=16% (Being the remainder).

I have tried to nest multiple IF statements.

1) IF(NOT(MOD(J13,3), SUM(H16:J16) ----> If current time period is a multiple of 3, sum the current month + the previous 2 months.

2) IF(P13=$E$13,SUM(O16:P16) ----> If current month equals the end on the project time period, only sum the current month %.

The problem with this is what happens if the project time period = 7 months. The 7th month % cost should equal only the 7th Month%, (not 7th month% +6th month% - as indicated by the formula). The previous payment was on the 6th month (only 1 month ago).


This would also be a problem is the project time period was 4 months, for instance.

I also need the negative result to equal 0, not false.

If anyone can help, I would be most appreciative.

Kind Regards,
Mitch

Hi Ace_XL

I can't get it working using this formula, maybe it is me, but it just doesn't work.

Thank you for your reply, regardless.

Can you / or anyone else provide an alternative solution?

I would be most appreciative!

Kind Regards, Mitchell

Thanks for your attempts to solve the problem, clearly my questions are not concise. I will re-post the problem with a clearer xls. document.

Thanks Again

Quarterly Cost Payments % - Take2

Hi Guys/Girls
I Have made my questions and xls. document attached more concise:

Question:
I am trying to assign %'s for a Property Holding Cost. I cannot formulate the correct IF statements to correctly calculate the below-mentioned equation.

1 2 3 4 5 6 7 8 9 10 Time Period (Months)
10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Cumulative Cost % assignment
10% 10% 10% 10% 10% 10% 10% 10% 10% 10% Spread Cost % assignment

The cost needs to be paid quarterly, i.e. month cost % 1,2&3 are totaled in Month 3. When the property is sold, the remaining 10th month holding cost %(ie.) 1 Month (10%) is totaled to finalize the Sale.

Example: Holding Cost is $12,000, paid quarterly, the Property is sold at the end of the 10th Month.

i.e. Time Period 1-0%($0), 2-0%($0), 3-30%($3,600), 4-0%($0), 5-0%($0), 6-30%($3,600), 7-0%($0), 8-0%($0), 9-30%($3,600), 10 -Property is sold Remaining 10%($1,200).

See attached Excel: Row 17 is highlighted in yellow, the correct equation must be able to be filled across.

Thanks to anyone that can help me here

Kind Regards,

Mitchell

I am really stuck here, is there anyone that can help? I would greatly appreciate it

Warm Regards,
Mitchell