## Hot answers tagged worksheet-function

3

Depending on your interest rate (monthly here), you need something set up like this -
Basically, you need to calculate the debt based on the previous debt with a formula like this -
Now, if there are penalties for missing a payment, you can add those in by using a formula like this-
=(C2*$F$1)+(C2-B3)+50*IF(ISBLANK(B3),1,0)
Where the fine is $50 for ...

2

Here's one way you can manage it on your own. This is different than setting up an amortization payoff because it accommodates flexible repayment. If you'd prefer to adhere to a fixed monthly repayment of $111.25 or something, this is less useful. Otherwise...
Just add a line every month (I chose the 1st day of each month) and add a line every time there's ...

1

If I understand correctly, you want to test how many times a given customer has made a donation (CC or SO) previously, so this is effectively a running count of how many donations this customer has made.
Try this formula in P2 and copy down:
=IF(OR(J2="CC",J2="SO"),SUM(COUNTIFS(A$1:A2,A2,J$1:J2,{"CC","SO"})),0)
Explanation: tests if the line is a CC or ...

1

The rounding functions work at the place level. You could use:
=50*INT(A1/50)
However, Ron Rosenfeld's answer is simpler.
If you wanted to round to a multiple instead of rounding down, you could use MROUND:
=MROUND(A1,50)

1

Damn, that was way easier than I imagined. Sumproduct alows you to do array-like manipulations on the individual values before you add them up
=SUMPRODUCT((A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365)*C2:C6*((B2:B6)-(B6-365))/365)
The (A2:A6=A6)*(B2:B6<B6)*(B2:B6>B6-365) are the conditions
The C2:C6 are the values that are to be considered (if they meet ...

1

I found a (partial) solution to this problem, but it may not work in your specific situation. The Office documentation for the HYPERLINK function provides a clue.
In my testing, importing a file of plain URLs into an Excel column where another column is already filled with HYPERLINK formulas that refer to the URL column results in the column with the ...

Only top voted, non community-wiki answers of a minimum length are eligible