Q: pop-up menu
Hi everyone!
I was wondering if Numbers has a function similar to
CASE [Region]: WHEN "Monthly" THEN 1, WHEN "QUARTERLY" THEN 2, WHEN "Yearly" THEN 3, etc.
This is the logics I thinking of when trying to calculate the monthly expenses for my household budget, and some payments occur on a different basis (weekly, monthly, per quarter, every 6 months, etc.).
So, I have a cell A1 with a pop-up menu where these are listed, and a cell B1 where I type in the amount.
In cell C1, I would like to calculate the monthly expense based on the selected item in A1.
Now, it looks like this:
C1=IF (A1="Monthly"; B1; IF (A1="Quarterly"; B1÷4; "smile") )
May be there is an easier way?
Thanks,
Adriana
MacBook Air, OS X El Capitan (10.11.6), null
Posted on Oct 13, 2016 12:45 PM
"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:
- I mistyped the last number in column C—40 instead of 49
- I corrected the divisor for the Quarterly choice to match the 3 months that make up a quarter.
B.
Posted on Oct 15, 2016 5:52 AM




