::undefined::undefinedTo create a cash-flow spreadsheet of balances
overdue by 30, 60 and 90 days respectively, as well as those same
increments upcoming in the future, I painstaking built the formula:
*=IF(AND(Y100,Y10<=30),X10,"")*, where X10 is the balance overdue by
0-30 days. The spreadsheet works fine because the columns for 31-60
days, 61-90 days, and 90 days each contain the appropriate values for
Y10, respectively.

I copied and modified these four columns to eight to see who had future
balances by the same four increments. The resuslt is a clean spreadsheet
where eight columns return accurate results as the balances age.

But, all that was for Installment #1 only. I just learned that I'm
expected to display, on the same spreadsheet, similar results for
Installments #2, #3 and even #4 if they exist. And my boss doesn't want
a 32-column spreadsheet!

Since I can't put more than one formula in one cell, can I nest my
formula within other IF(AND formulas or am I on the wrong track here?
I've been researching Arrays but I don't think that's the answer.

Any help would be greatly apppreciated as I'm nearly ready to replicate
the successful spreadsheet on three additional tabs which would mean
that management would have to toggle among four separate tabs to see
who owes what at any given time.

Thank you very much.


--
wellfm
------------------------------------------------------------------------
wellfm's Profile: http://www.excelforum.com/member.php...o&userid=27797
View this thread: http://www.excelforum.com/showthread...hreadid=473028

What do you mean by installment 1,2,3 4?

"wellfm" wrote:


::undefined::undefinedTo create a cash-flow spreadsheet of balances
overdue by 30, 60 and 90 days respectively, as well as those same
increments upcoming in the future, I painstaking built the formula:
*=IF(AND(Y100,Y10<=30),X10,"")*, where X10 is the balance overdue by
0-30 days. The spreadsheet works fine because the columns for 31-60
days, 61-90 days, and 90 days each contain the appropriate values for
Y10, respectively.

I copied and modified these four columns to eight to see who had future
balances by the same four increments. The resuslt is a clean spreadsheet
where eight columns return accurate results as the balances age.

But, all that was for Installment #1 only. I just learned that I'm
expected to display, on the same spreadsheet, similar results for
Installments #2, #3 and even #4 if they exist. And my boss doesn't want
a 32-column spreadsheet!

Since I can't put more than one formula in one cell, can I nest my
formula within other IF(AND formulas or am I on the wrong track here?
I've been researching Arrays but I don't think that's the answer.

Any help would be greatly apppreciated as I'm nearly ready to replicate
the successful spreadsheet on three additional tabs which would mean
that management would have to toggle among four separate tabs to see
who owes what at any given time.

Thank you very much.


--
wellfm
------------------------------------------------------------------------
wellfm's Profile: http://www.excelforum.com/member.php...o&userid=27797
View this thread: http://www.excelforum.com/showthread...hreadid=473028

bj Wrote:
What do you mean by installment 1,2,3 4?

Well, by 1,2,3,4 I mean that my original formula returns blanks where
balances are -not - overdue and values where balances are overdue by
either 30, 60, or 90 days, or, are coming due within 30, 60 or 90 days
in the future. That all works fine with -one - returned balance
"skipping" across 8 cells as I test-change the date due.

But, all of this is for the *First Installment * only. In most cases
cases, since these payments are actually gift pledges, there can be 2,
3 or 4 installments set up at regular or irregular intervals based on
the original pledge.

E.g., Joe Doakes might have pledged $4,000, payable in four annual
installments, each due on 12/31. When 2006 begins, he will immediately
be 0-30 days late with Installment #1, but somehow, I'm expected to
also show, on the same row, that when 2007 begins, he will be owing us
for Installment #2. Does that make any sense, especially since I don't
want a spreadsheet that cumbersomely long.

Eventually, I will be porting all this into Crystal Reports with its
greater flexibility, but for now, I must show it all in Excel.

Thanks.


--
wellfm
------------------------------------------------------------------------
wellfm's Profile: http://www.excelforum.com/member.php...o&userid=27797
View this thread: http://www.excelforum.com/showthread...hreadid=473028

What you might try is to have just two columns for each installment
Not knowing what your spreadsheet looks like, my guesses may be totally wrong
in one cell have the doners name
adjacent to it have a cell with something like
=if(criteria for on time met,"current",if(criteria for 30 to 60 days late
met,"more than 30 days late",if (criteria for 60-90 days late met,"more than
60 days late","more than 90 days late")))
the next cell with the amount late
repeat these cells for the different installments.
the final eqwuations will really depend on what your page set up is.


"wellfm" wrote:


bj Wrote:
What do you mean by installment 1,2,3 4?

Well, by 1,2,3,4 I mean that my original formula returns blanks where
balances are -not - overdue and values where balances are overdue by
either 30, 60, or 90 days, or, are coming due within 30, 60 or 90 days
in the future. That all works fine with -one - returned balance
"skipping" across 8 cells as I test-change the date due.

But, all of this is for the *First Installment * only. In most cases
cases, since these payments are actually gift pledges, there can be 2,
3 or 4 installments set up at regular or irregular intervals based on
the original pledge.

E.g., Joe Doakes might have pledged $4,000, payable in four annual
installments, each due on 12/31. When 2006 begins, he will immediately
be 0-30 days late with Installment #1, but somehow, I'm expected to
also show, on the same row, that when 2007 begins, he will be owing us
for Installment #2. Does that make any sense, especially since I don't
want a spreadsheet that cumbersomely long.

Eventually, I will be porting all this into Crystal Reports with its
greater flexibility, but for now, I must show it all in Excel.

Thanks.


--
wellfm
------------------------------------------------------------------------
wellfm's Profile: http://www.excelforum.com/member.php...o&userid=27797
View this thread: http://www.excelforum.com/showthread...hreadid=473028