Home

Return value only in rows specified by entering a number in an exterior cell to the table

BRaff
Occasional Visitor

I have an amortization table and I want to be able to calculate extra payments on the loan by entering the extra payment amount in a cell as well as the payment numbers and have the table return those values in the extra payment column only if they correspond to the specified payment number in the column to the left.  (Ex. **see photo** L6=$1,000, L7=3,5:7,10,15... Table Column [ExtraPayments] should have $1,000 in the rows which have 3,5:7,10,15 in the [PMT NO] column on the left ([PMT NO] is not the same as the rows since the table starts in row 12) in the photo you see my current formula has it showing in every cell, I need the $1,000 to only show in the specified locations).  Does this make sense?  I don't really know how to explain it.

 

InkedExcel Capture_LI.jpg

2 Replies

You may enter this formula in F12, copied down the rows:
=SUMPRODUCT(({3,5,6,7,10,15}=B12)*1000)

@BRaff 

@Twifoo's solution is elegant and compact but relies upon hard-coding both the extra payment periods and amount within the formula.  To avoid this some way of parsing or searching the comma-separated string is needed.  I chose the latter, first padding the list with additional commas to give a name ONE.TIME that refers to

= comma & ONE.TIME.LIST & comma

where 'comma' refers to 

= ","

(just to improve legibility).

 

The test to determine whether the payment number has an associated one-time payment is

= ISNUMBER( SEARCH( comma & amortization[@[PMT NO.]] & comma, ONE.TIME) )

which is held as a named formula 'ONE.TIME.NO?'.

 

Finally to populate the 'EXTRA PAYMENT' column (your capitals, I am not shouting at you :-)

= IF( ONE.TIME.NO?, ONE.TIME.AMOUNT, "" )

 

Another catch is that the user may embed spaces in the list of one-time payments, in which case an extra processing step is needed to replace them with null strings.