Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Christian-A Author

Level 1

4 points

Formula for percentage based on name...

Hi Everyone,

This is my first time here and I am new to Numbers. I apologize if this has already been asked but I searched quite a bit and could not find anything similar.

I am working on a spreadsheet to calculate bank compensation schedules and would like to create the following:

Cell D2 has a loan amount (ex. $500,000)

Cell E2 has a dropdown of different bank names.(FSB, UWM, etc.)

Cell F2, I would like to be able to calculate a percentage of cell D2, depending on which bank was selected. In other words, If I select FSB, then I would like F2 to show 1.25% of D2, but if I select UWM, I would like F2 to show 1.00% of D2.

Is this possible? Thank you in advance for your time and help.

Christian

iMac, macOS Sierra (10.12.6)

Posted on Dec 11, 2017 12:56 PM

Reply

Best reply

Level 7

26,529 points

Posted on Dec 11, 2017 1:17 PM

you can create a separate table to contains the list of banks and the corresponding percentage... like this:

The first row of this table should be a header row.

Now select the list of bank names in the table "Bank List", copy:

Now in a second table (shown on the right, "Table 1"). Add a header row, and add a column to select the bank name.

select cell B2, paste

now select the bank names you just pasted in the table on the right, then format as a pop-up in the cell formatter:

select cells B2, copy

select cells B2 thru the end of column B, paste

Now add the formula to lookup the percentage by selecting cell C2 and typing the formula:

=VLOOKUP(B2, Bank List::A:B, 2, 0)

select cell C2, copy

select cells C2 thru the end of column C, paste

now change the pop-up menus in column B

View in context

2 replies

Best reply

Level 7

26,529 points

Dec 11, 2017 1:17 PM in response to Christian-A

you can create a separate table to contains the list of banks and the corresponding percentage... like this:

The first row of this table should be a header row.

Now select the list of bank names in the table "Bank List", copy:

Now in a second table (shown on the right, "Table 1"). Add a header row, and add a column to select the bank name.

select cell B2, paste

now select the bank names you just pasted in the table on the right, then format as a pop-up in the cell formatter:

select cells B2, copy

select cells B2 thru the end of column B, paste

Now add the formula to lookup the percentage by selecting cell C2 and typing the formula:

=VLOOKUP(B2, Bank List::A:B, 2, 0)

select cell C2, copy

select cells C2 thru the end of column C, paste

now change the pop-up menus in column B

Reply

Christian-A Author

Level 1

4 points

Dec 11, 2017 5:53 PM in response to Christian-A

Hi Wayne,

Thank you so much for the quick and effective response. This took care of my problem and I was also able to apply it to a few different things.

I appreciate your time. Have a great night!

Reply

Formula for percentage based on name...

Welcome to Apple Support Community

A forum where Apple customers help each other with their products. Get started with your Apple ID.

Learn more Sign up