Hi,
This is my first time posting, hopefully someone can help.
I have a list of times and a matching list of payments - Times in cells C4:C14 and payments in cells D4:D14.
I have formulas to show the time of the next payment in cell F4 {=MIN(IF(C4:C14>(NOW()-TODAY()),C4:C14))}
and one to show the next payment amount in cell G4 =INDEX(D4:D14,MATCH(F4,C4:C14,0))
The bit I am struggling with is a formula to find the time when my balance (in cell F7) plus the sum of future payments reaches 10.
At the moment I have the following formula which involves many nested IF statements.
=IF(F7+INDIRECT("D"&H7)<10,
IF(F7+INDIRECT("D"&H7+1)<10,
IF(F7+INDIRECT("D"&H7+2)<10,
IF(F7+INDIRECT("D"&H7+3)<10,
IF(F7+INDIRECT("D"&H7+4)<10,
IF(F7+INDIRECT("D"&H7+5)<10,
IF(F7+INDIRECT("D"&H7+6)<10,
IF(F7+INDIRECT("D"&H7+7)<10,
IF(F7+INDIRECT("D"&H7+8)<10,
IF(F7+INDIRECT("D"&H7+9)<10,
IF(F7+INDIRECT("D"&H7+10)<10,
"OVER 10",
INDIRECT("C"&H7+10)),
INDIRECT("C"&H7+9)),
INDIRECT("C"&H7+8)),
INDIRECT("C"&H7+7)),
INDIRECT("C"&H7+6)),
INDIRECT("C"&H7+5)),
INDIRECT("C"&H7+4)),
INDIRECT("C"&H7+3)),
INDIRECT("C"&H7+2)),
INDIRECT("C"&H7+1)),
INDIRECT("C"&H7))
In this formula the cell H7 is used as the row counter for the next payment, derived from cell F4.
It is functional in this example data but not practical in my real data as there are many more than 64 entries which I believe is the maximum number of nested IF statements.
Is there an easier and nicer way to find the next payment and then add the SUM of future payments to my balance until a value of 10 is reached? At this point I would like to display the time at which a balance of 10 is reached.
I hope this is clear and thanks in advance for any help.
This is my first time posting, hopefully someone can help.
I have a list of times and a matching list of payments - Times in cells C4:C14 and payments in cells D4:D14.
I have formulas to show the time of the next payment in cell F4 {=MIN(IF(C4:C14>(NOW()-TODAY()),C4:C14))}
and one to show the next payment amount in cell G4 =INDEX(D4:D14,MATCH(F4,C4:C14,0))
The bit I am struggling with is a formula to find the time when my balance (in cell F7) plus the sum of future payments reaches 10.
At the moment I have the following formula which involves many nested IF statements.
=IF(F7+INDIRECT("D"&H7)<10,
IF(F7+INDIRECT("D"&H7+1)<10,
IF(F7+INDIRECT("D"&H7+2)<10,
IF(F7+INDIRECT("D"&H7+3)<10,
IF(F7+INDIRECT("D"&H7+4)<10,
IF(F7+INDIRECT("D"&H7+5)<10,
IF(F7+INDIRECT("D"&H7+6)<10,
IF(F7+INDIRECT("D"&H7+7)<10,
IF(F7+INDIRECT("D"&H7+8)<10,
IF(F7+INDIRECT("D"&H7+9)<10,
IF(F7+INDIRECT("D"&H7+10)<10,
"OVER 10",
INDIRECT("C"&H7+10)),
INDIRECT("C"&H7+9)),
INDIRECT("C"&H7+8)),
INDIRECT("C"&H7+7)),
INDIRECT("C"&H7+6)),
INDIRECT("C"&H7+5)),
INDIRECT("C"&H7+4)),
INDIRECT("C"&H7+3)),
INDIRECT("C"&H7+2)),
INDIRECT("C"&H7+1)),
INDIRECT("C"&H7))
In this formula the cell H7 is used as the row counter for the next payment, derived from cell F4.
It is functional in this example data but not practical in my real data as there are many more than 64 entries which I believe is the maximum number of nested IF statements.
Is there an easier and nicer way to find the next payment and then add the SUM of future payments to my balance until a value of 10 is reached? At this point I would like to display the time at which a balance of 10 is reached.
I hope this is clear and thanks in advance for any help.