instantsoul

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

Close

Q: pop-up menu

  • All replies
  • Helpful answers

  • by Barry,

    Barry Barry Oct 13, 2016 4:17 PM in response to instantsoul
    Level 7 (32,660 points)
    iWork
    Oct 13, 2016 4:17 PM in response to instantsoul

    HI Adriana,

     

    A lookup Table would likely do the job.

    Screen Shot 2016-10-13 at 4.07.39 PM.png

    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

  • by instantsoul,

    instantsoul instantsoul Oct 14, 2016 1:50 PM in response to Barry
    Level 1 (4 points)
    iWork
    Oct 14, 2016 1:50 PM in response to Barry

    Hi Barry,

    Thank you very much for your reply

     

    Actually, I am trying to do it a little bit differently, since I would like to have the average planned expenses per month in one column and the actual in another - all this in one table.

     

    Screen Shot 2016-10-14 at 22.32.08.png

     

    Hence, 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.

     

    Currently, the calculations in column E are made with nested IF's, like this :

    E7= IF(D7="Monthly";Amount Beachvolley;IF(D7="Quarterly";Amount Beachvolley÷4;IF(D7="Half year";Amount Beachvolley÷6;IF(D7="Yearly";Amount Beachvolley÷12;Amount Beachvolley;A7))))

     

    However, the more items the longer the formula becomes.

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

     

     

    Thanks,

    Adriana

  • by Barry,Solvedanswer

    Barry Barry Oct 15, 2016 5:52 AM in response to instantsoul
    Level 7 (32,660 points)
    iWork
    Oct 15, 2016 5:52 AM in response to instantsoul

    "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?

    Screen Shot 2016-10-14 at 4.54.38 PM.png

    (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...

    Screen Shot 2016-10-14 at 5.13.21 PM.png

    Producing this view of the same table:

    Screen Shot 2016-10-14 at 5.14.08 PM.png

    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.

  • by instantsoul,

    instantsoul instantsoul Oct 15, 2016 6:25 AM in response to Barry
    Level 1 (4 points)
    iWork
    Oct 15, 2016 6:25 AM in response to Barry

    Amazing, Barry!

    Thank you so much, your second post really solved my issue!

     

    Not only that, it was so explicit and well structured that I got to understand how the VLOOKUP function works. Thanks!

    It was one of the functions I looked at as a possible solution, but did not ended up using. It did not make much sense to me despite reading few times through the given example in Numbers.

     

    Have a great day,

    Adriana