Totally Lost

[deyn]

Hi All,

I would say i'm half decent with Excel but my requirement has get my totally lost.

Basically I'm trying to find the correct formula based on certain text requirement.

For Example :

The requirements in cell N2 needs to be either "Type1", "Type2", "Type3", "Type4"

then if N2 states ;

Type 1, I require a multiply sum P2(value)*Q2(value*0.28*2.2
Type 2, I require a multiply sum P2(value)*Q2(value*0.245*2.2
Type 3, I require a multiply sum P2(value)*Q2(value*0.06*2.2
Type 4, I require a multiply sum P2(value)*Q2(value*0.06*2.2

Any help would be much appreciated please

 

[Legacy 143009]

=P2*Q2*LOOKUP(N2,{"Type 1","Type 2","Type 3","Type 4"},{0.28,0.245,0.06,0.06})*2.2

 

[James006]

Hi,
You can use all the common elements in your formula and have

=CHOOSE(SUBSTITUTE(N2,"Type ",""),0.28,0.245,0.06,0.06)

 

[deyn]

=P2*Q2*LOOKUP(N2,{"Type 1","Type 2","Type 3","Type 4"},{0.28,0.245,0.06,0.06})*2.2

Thank you, this has worked.

What I forgot to add is, it's possible N2 would have alternate text in that doesn't need to have a sum calculated but like a return of "False" if possible please?

 

[Legacy 143009]

=IFNA(LOOKUP(...), FALSE)

Or:

=IF(ISNA(LOOKUP(N2,{"Type 1","Type 2","Type 3","Type 4"})), FALSE, P2*Q2*LOOKUP(N2,{"Type 1","Type 2","Type 3","Type 4"},{0.28,0.245,0.06,0.06})*2.2)

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Complex Formula - Lost

Hi All, I'm not sure even this is even possible, but I'm trying to build a sheet for my work which will take alot of the manual work out of the sheet. I think I need 3 different formula's, with formula 1 being basic I think, and 2 & 3 more complex Formula 1 If text in column N...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.