Defining variables and percentages

TheDiego

New Member
Joined
Oct 29, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I am back for more assistance,

This time working on a list with pricing.

I categorize percentages into ID codes, for example:

ID code "1"= 40%
ID code "2" = 30%
ID code "3" = 20%
ID code "4" = 10%

I have a table that is divided into ID, Price and Discounted price.

What I am trying to do is essentially:

If "ID"= 1, then {List Price * 40%}
If "ID"= 2, then {List Price * 30%}
And so forth...

I have an idea of how to tackle it, but my main issue is defining the variable "1" as 40%.

I would appreciate your assistance.

Have a great day :)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi, here's one option you can try.

Book1
ABC
1IDPriceDiscounted Price
21104
3210030
431000200
515020
62144.2
74515.1
81156
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=B2*CHOOSE(A2,40%,30%,20%,10%)
 
Upvote 0
Hi FormR,

Thank you for your quick response.

I experimented with the below.

However, I imagine the variable "1" is already defined to 40%?

How can I make it recognize 1 as 40%, 2 as 30% and so on?

Do I need to do something in VBA?

Please let me know.

Hi, here's one option you can try.

Book1
ABC
1IDPriceDiscounted Price
21104
3210030
431000200
515020
62144.2
74515.1
81156
Sheet1
Cell Formulas
RangeFormula
C2:C8C2=B2*CHOOSE(A2,40%,30%,20%,10%)
 
Upvote 0
Do I need to do something in VBA?

Hi, my suggestion was a formula that produced the results in column C in my example - if that's not what you're after you might need to have another go at describing what you do actually want.

Perhaps some example data and expected results would help to clarify.
 
Upvote 0
Hi, my suggestion was a formula that produced the results in column C in my example - if that's not what you're after you might need to have another go at describing what you do actually want.

Perhaps some example data and expected results would help to clarify.

Hi FormR,

I understand and thank you for your reply.

What is still unclear is how the formula determines the 40%, 30%, 20%, 10%.

How do you state 1 = 40% and so forth?

1649688650957.png


This is what I get when I input the formula.
 
Upvote 0
How do you state 1 = 40% and so forth?

Hi - the CHOOSE() functions first argument tells it which of the following arguments to return.

Take a look here:
CHOOSE function

This is what I get when I input the formula.

Does you locale require you to use semicolons instead of commas as the list separator in formulas? If so, try like this.

Excel Formula:
=B2*CHOOSE(A2;40%;30%;20%;10%)
 
Upvote 0
Hi,

I tried with the semicolons and it worked!

Thank you very much.

Have a great day :).

Hi - the CHOOSE() functions first argument tells it which of the following arguments to return.

Take a look here:
CHOOSE function



Does you locale require you to use semicolons instead of commas as the list separator in formulas? If so, try like this.

Excel Formula:
=B2*CHOOSE(A2;40%;30%;20%;10%)
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top