You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

User profile for user: RayvlinJohn
RayvlinJohn Author
User level: Level 1
10 points

Using Pop-ups in a Formula

Hi!


Im trying to make a formula involving pop-up menus to help direct to specific values.


For example:


= A2 x B2


Wherein A2 is a number and B2 is a pop-up menu with different sizes (Small, Medium, and Large). I was wondering if it was possible to configure it so that when i choose Small, Medium, or Large, the values 50%, 75%, or 100% will be used respectively. The values may also change overtime so i was thinking if it was also possible to simply change the values anytime, and the formula would be updated automatically.


Thanks!

Posted on Feb 21, 2022 12:00 AM

Reply
Question marked as Top-ranking reply
User profile for user: SGIII
SGIII
User level: Level 8
36,278 points

Posted on Feb 21, 2022 3:14 AM

One way is to do something like this:




=A2*INDEX({50%,75%,100%},MATCH(B2,{"Small";"Medium";"Large"},0))


Or if your regions uses , as a decimal separator:

=A2*INDEX({50%.75%.100%};MATCH(B2;{"Small";"Medium";"Large"};0))


The formula checks what value you have in B2 and then looks up the corresponding %.


When the % values change you could just change them right in the formula.


If you expect them to change a lot you could put them in a column in a separate table and have the formula look them up there instead of within the { }.


Note that a Pop-Up Menu is best left simple, three possible values here, without trying to somehow include % values within the Pop-Up Menu itself.


SG

View in context

Similar questions

2 replies
Question marked as Top-ranking reply
User profile for user: SGIII
SGIII
User level: Level 8
36,278 points

Feb 21, 2022 3:14 AM in response to RayvlinJohn

One way is to do something like this:




=A2*INDEX({50%,75%,100%},MATCH(B2,{"Small";"Medium";"Large"},0))


Or if your regions uses , as a decimal separator:

=A2*INDEX({50%.75%.100%};MATCH(B2;{"Small";"Medium";"Large"};0))


The formula checks what value you have in B2 and then looks up the corresponding %.


When the % values change you could just change them right in the formula.


If you expect them to change a lot you could put them in a column in a separate table and have the formula look them up there instead of within the { }.


Note that a Pop-Up Menu is best left simple, three possible values here, without trying to somehow include % values within the Pop-Up Menu itself.


SG

Reply

Using Pop-ups in a Formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up