VBA If Else Help

[DalPai]

Hello everyone!


I have a "problem" in a spreadsheet that I believe through VBA I could solve. But I never programmed in VBA, so I have no idea where to start.

I have a table with the following information: Project Name, Type, Stage, Value, Adjusted Value.

The adjusted value depends on the stage the project is.
ex: If a project of value 100 is in the Idea stage, its adjusted value is 100*10% = 10

I was able to solve this simply by using =IF in excel.

However, the percentage will also change according to the Project type. And writing such a formula using =IF would be huge.


I believe that by VBA I can do this easily, but where to start?

Best Regards

 

[Fluff]

How many project types are there?

 

[Joe4]

You may be able to accomplish what you want without VBA. You can set up a lookup table that has your various Projects and Stages and associated value, and then you can use an Index/Match formula to look up the appropriate value from that table.
See here: https://spreadsheeto.com/index-match/

 

[DalPai]

Hi Fluff,

There are 7 types, but the % changes only with one Type.

Type: Core
Stage 0: 0%
Stage 1: 15%
Stage 2: 25%
Stage 3: 40%
Stage 4: 50%
Stage 5: 100%

Type: Other 6
Stage 0: 0%
Stage 1: 5%
Stage 2: 20%
Stage 3: 40%
Stage 4: 90%
Stage 5: 100%

 

[Fluff]

Ok, how about

Book1
	A	B	C	D	E
1	Project Name	Type	Stage	Value	Adjusted Value
2		Core	Stage 0	100	0
3		Core	Stage 1	100	15
4		Core	Stage 2	100	25
5		Core	Stage 3	100	40
6		Core	Stage 4	100	50
7		Core	Stage 5	100	100
8		??	Stage 0	100	0
9		??	Stage 1	100	5
10		??	Stage 2	100	20
11		??	Stage 3	100	40
12		??	Stage 4	100	90
13		??	Stage 5	100	100
RC

Cell Formulas
Range	Formula
E2	=D2*IF(B2="Core",CHOOSE(RIGHT(C2,1)+1,0,0.15,0.25,0.4,0.5,1),CHOOSE(RIGHT(C2,1)+1,0,0.05,0.2,0.4,0.9,1))

 

[DalPai]

You may be able to accomplish what you want without VBA. You can set up a lookup table that has your various Projects and Stages and associated value, and then you can use an Index/Match formula to look up the appropriate value from that table.
See here: https://spreadsheeto.com/index-match/

I completely forgot about the INDEX/MATCH formula.

Worked like a charm. Thank you very much

 

[Joe4]

You are welcome.
Glad we were able to help.