pop-up menu

HI Adriana,

A lookup Table would likely do the job.

Price list (had it available and left the name the same( 😁 )

Lists the payment/deposit frequency choices in column A, and the fraction of a year represented by one payment in column B.

Piggy Bank

Uses Pop-up menu cells to choose the payment frequency in column A.

Uses the formula below in each cell of column B to determine the amount needed for each deposit or payment to reach the goal (or pay off the interest-free loan) shown in B1, in 1 year.

Pop-up: List all the choices. Select the cells containing the list. Open the Cell Format Inspector and choose Pop-up menu from the format list. Change "Start with first item" to "Start With Blank" to permit including 'blank' cells.

Formula:

B2: IF(ISBLANK(A2),"",B$1×VLOOKUP(A2,Price list::A:B,2,FALSE))

Fill down to end of column B

Regards,

Barry

"However, the more items the longer (and more difficult to edit) the formula becomes."

Which is a pretty good description of what does not happen when you use a lookup table and a formula that usually requires no changes once it's been entered.

This is why I posted the question over here, hoping for a hint of a more elegant formula solution."

Which is what you get with a solution using a lookup table.

"I love the opportunity of having a pop-up menu, it is really handy and the view is a bit more simple and tidy compared to two tables so, I am not sure that I will easily give up on it."

You mean a 'simple and tidy view' like this?

(I've omitted the data in columns not involved in the calculations.)

No need to forego pop-up menus—as indicated by the control visible to the right of the selected cell, it (and all of the cells in column D except D1 and D14) are pop-up menu cells.

Column D contains this formula, entered in D2, then filled down to D13.

D2: IF(ISBLANK(D2),"",C2÷VLOOKUP(D2,G:H,2,FALSE))

The part in bold is the core formula that does the calculations. The IF part is a switch that prevents the calculation until a menu choice is made in column D.

The image above shows the table as it would appear in day to day use. If a need to edit the lookup table, all that's required is a visit to the Table menu to Unhide all columns...

Producing this view of the same table:

When done, select the two columns containing the lookup table, and go Table (menu) > Hide 2 columns (or use the pop-up menu in the column reference tab for one of the selected columns) to get back to the view above.

Regards,

Barry

PS: You may have noticed my 'per month' total is different from yours. There are two factors at work here:

1. I mistyped the last number in column C—40 instead of 49
2. I corrected the divisor for the Quarterly choice to match the 3 months that make up a quarter.

B.