Next Payment - Multiple of $5k is reached

[Sara]

Hi there

I've created a loan sheet detailing payment dates and how much has been paid off.
I want to celebrate when I first reach each multiple of $5,000 (i.e $5,000,$10,000, $15,000 etc)
How do I write a formula without a helper column to return the next payment date where a multiple of $5,000 is reached?

I've uploaded a file with the data, and how I did it with a helper column.

 

[xlstime]

Hey Sara

please use below formula

Q3 = =MIN(IFERROR(IF((YourTable[Date]>TODAY())*(FLOOR(P5 : P679,5000)<>FLOOR(P6 : P681,5000)),YourTable[Date]),99999999))

with ctrl+shift+enter (array formula)

Hi there

I've created a loan sheet detailing payment dates and how much has been paid off.
I want to celebrate when I first reach each multiple of $5,000 (i.e $5,000,$10,000, $15,000 etc)
How do I write a formula without a helper column to return the next payment date where a multiple of $5,000 is reached?

I've uploaded a file with the data, and how I did it with a helper column.

 

[Sara]

Thanks for that.
So I understand it and can adapt to use elsewhere, the formula in M3 is this:

=MIN(IFERROR(IF((YourTable[Date]>TODAY())*(FLOOR(M5:M679,5000)<>FLOOR(YourTable[Paid Off?],5000)),YourTable[Date]),99999999))

There is no built in formula for a MINIF so an array is used.

The IF statement: Logic Portion
YourTable[Date]>TODAY(); becomes TRUE & FALSE array

FLOOR(M5:M679,5000); becomes multiples of 5000
why does it include the header?
why is this not the full range of data?
why that portion (not more or less rows)?
This array returns TRUE & FALSE if it's not equal to the array of the Floor of the whole data range

Combining these two with an * results in the only one value in the combined array that is True, effectively a multiple of 5000 in the future that is the first time it's reached. The remainder being false or an error

The IF statement: Value if True portion is all the dates in the range
So the result is an array of errors, falses and one date

The IFERROR is there to handle the errors in the IF and return an extremely high number. So this results in an array of falses, high numbers and one date

The minimum is then going to return the one date

 

[xlstime]

OK,

why does it include the header? - As we need to check difference (multiple of $5,000) between last paid off and current paid off that why have checked first paid off with header.


why is this not the full range of data? - you can increase range as per your requirement
why that portion (not more or less rows)? -you can increase range as per your requirement

Thanks for that.
So I understand it and can adapt to use elsewhere, the formula in M3 is this:

=MIN(IFERROR(IF((YourTable[Date]>TODAY())*(FLOOR(M5:M679,5000)<>FLOOR(YourTable[Paid Off?],5000)),YourTable[Date]),99999999))

There is no built in formula for a MINIF so an array is used.

The IF statement: Logic Portion
YourTable[Date]>TODAY(); becomes TRUE & FALSE array

FLOOR(M5:M679,5000); becomes multiples of 5000
why does it include the header?
why is this not the full range of data?
why that portion (not more or less rows)?
This array returns TRUE & FALSE if it's not equal to the array of the Floor of the whole data range

Combining these two with an * results in the only one value in the combined array that is True, effectively a multiple of 5000 in the future that is the first time it's reached. The remainder being false or an error

The IF statement: Value if True portion is all the dates in the range
So the result is an array of errors, falses and one date

The IFERROR is there to handle the errors in the IF and return an extremely high number. So this results in an array of falses, high numbers and one date

The minimum is then going to return the one date

 

[Sara]

Thank you for that.
My next goal in excel is to understand array formulas, I'm getting there slowly

 

[Nebu]

Hi:

You can use the following non-array formula as well.

=AGGREGATE(15,6,YourTable[Date]/((YourTable[Date]>TODAY())*(FLOOR(YourTable[[#All],[Paid Off?]],5000)<>FLOOR(YourTable[Paid Off?],5000))),1)

Thanks

 

[Sara]

Think I need to learn about aggregates too - cheers for that