Execute different formulae based on different value in two different cells

[Djinn101]

Hi People

I am trying to calculate a benefit $ value for a project. The project provides different benefits in different scenarios. I have to parameters with multiple value for scenarios.

Parameter 1 - Is the project targeted at children?
Values - Yes, No

Parameter 2 - Which ice cream flavours are served to the children?
Values - Vanilla, Jackfruit, Strawberry, Almond

Now I would like that the parameters can be selected through a drop down (can be down through simple Data Validation). More importantly, I would like different forumulae to the executed by excel to calculate project value based on the combination of values for Parameters 1 and 2.

Please help.
Thanks a lot

 

[SteveO59L]

something along the lines of

=if(a1="yes",b1+b2,if(a1="no",c1+c2))

 

[Djinn101]

Hey SteveO59L
Thanks a lot for your prompt response. Here's how I see it

A1 and A2 are user programmable parameters with the following unique values which the user can define
A1 - Yes, No
A2 - vanilla, jackfruit, strawberry, almond

Cell D1 calculates the benefit in different ways based on which combination of values of A1 and A2 are selected by the user.

Now,

if A1=yes and A2=jackfruit, D1 should calculate benefit of project as C3+C2
if A1=yes and A2=vanilla, D1 should calculate benefit of project as C31+C8
if A1=yes and A2=almond, D1 should calculate benefit of project as C11+C22

if A1=no and A2=jackfruit, D1 should calculate benefit of project as C3+C1
if A1=no and A2=vanilla, D1 should calculate benefit of project as C11+C8
if A1=no and A2=almond, D1 should calculate benefit of project as C11+C8

(Please ignore the actual benefits formulae given above, these are simplified for the illustration)

Hence, essentially, D1 should somehow dynamically calculate the benefit using A1 and A2 as inputs. One example of a simple formula is the IF function. However, this situation has multiple values for A1 and A2 and would need a move evolved formula/approach.

Let me know in case the problem is still not clear. I'd be happy to explain this further.

Thanks again.

something along the lines of =if(a1="yes",b1+b2,if(a1="no",c1+c2))

 

[Peter_SSs]

Not sure what happened to strawberry, but could you create a lookup table that contained the various formulas then use VLOOKUP in D1?

Excel Workbook
	A	D	E	F	G
1	no	4		yes|vanilla	39
2	jackfruit			yes|jackfruit	5
3				yes|strawberry
4				yes|almond	33
5				no|vanilla	19
6				no|jackfruit	4
7				no|strawberry
8				no|almond	19
Different Formulas