Help choosing function

[jordiboronat]

Hi all,

This is my first post in here and hopefully you could help me.

I'm trying to set un a spreadsheet for engeneering purposes. I need to find properties from a fiber depending of different cells wich already have a drop down menu. All properties are related to formulas.

A1 is the type of fiber and has 3 possibilites (1,2,3)
A2 is the orientation and has 4 possibilities (1,2,3,4)
A3 it's a multiplier in the formula

Actually I'm using a comfination of "IF" and "AND" functions in the next way:
if(and(A1=1,A2=1),A3*number,0)+if(and(A1=1,A2=2),A3*number,0)+...

Obviously my if formula gets too long (12 "IF" including "AND" in brackets), anyone could suggest me a easier way to set up this please?

 

[jasonb75]

Welcome to the board!

How about a lookup (reference) table like the one below?

The sample formula will cross match fiber (A1) and orientation (A2), to find "number" in the table and multiply by A3.

For cleaner presentation the table could be set up on another sheet and hidden if needed.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD>Fiber</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>Orientation</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0.710492</TD><TD style="TEXT-ALIGN: right">0.629765</TD><TD style="TEXT-ALIGN: right">0.904313</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0.144139</TD><TD style="TEXT-ALIGN: right">0.217519</TD><TD style="TEXT-ALIGN: right">0.532265</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">0.432416</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0.14124</TD><TD style="TEXT-ALIGN: right">0.143194</TD><TD style="TEXT-ALIGN: right">0.417136</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">0.820763</TD><TD style="TEXT-ALIGN: right">0.186104</TD><TD style="TEXT-ALIGN: right">0.377871</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A4</TD><TD>=INDEX(E2:G5,MATCH(A2,D2:D5,0),MATCH(A1,E1:G1,0))*A3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[repairman615]

Hello,

Welcome to the board!

What are the different formulas you would like to return?

1. 1,1 = A3*number
2. 1,2 = A3*number
3. 1,3 =
4. 1,4 =
5. 2,1
6. 2,2
7. 2,3
8. 2,4
9. 3,1
10. 3,2
11. 3,3
12. 3,4

what would be the formulas for 3-12?

 

[AlphaFrog]

Maybe try something like this...
=A3*(HLOOKUP(VALUE(A1&A2),{11,12,13,14,21,22,23,24,31,32,33,34;1,2,3,4,5,6,7,8,9,10,11,12},2,0))

The green numbers are the values of A1 and A2 concatenated.
The Red values are the result you want returned from the lookup

Example:
A1 = 2
A2 = 3

The HLOOKUP looks up the value 23 in the green numbers and returns the corresponding value in the red numbers or 7 in this case. Then the 7 is multiplied by A3

 

[jordiboronat]

The problem is that it's a little bit more complicated. Let me explain it better.

The factor in A3 actually it's a number that goes into a formula. The fibers are Glass, Carbon and Aramid. The orientations are Woven roving, Unidirectional paralell to fibers and Unidirectional perpendicular. There's a 4th orientation although is not in this table.

What I'm actually trying to find is the value E(in-plane modulus), which is dependent of the fiber on cell A1, orientation A2 and value Ψ inputed in cell A3.

Attached you can find the table to get properties of E for glass fiber depending of orientation. of course there are two symilar tables for Carbon and Aramid fiber properties.

Thanks!



Uploaded with ImageShack.us

 

[repairman615]

What does numbers represent and also would you be able to provide the formulas for 3-12 from above. (the results of the selections in A2 & A3)

Right now it looks like you could just use =numbers*A3...but I am sure it can't be that simple.

 

[jasonb75]

Hi all,

This is my first post in here and hopefully you could help me.

I'm trying to set un a spreadsheet for engeneering purposes. I need to find properties from a fiber depending of different cells wich already have a drop down menu. All properties are related to formulas.

A1 is the type of fiber and has 3 possibilites (1,2,3)
A2 is the orientation and has 4 possibilities (1,2,3,4)
A3 it's a multiplier in the formula

Actually I'm using a comfination of "IF" and "AND" functions in the next way:
if(and(A1=1,A2=1),A3*number,0)+if(and(A1=1,A2=2),A3*number,0)+...

Obviously my if formula gets too long (12 "IF" including "AND" in brackets), anyone could suggest me a easier way to set up this please?

Based on the criteria in the red section, is there a constant value for "number" if A1 = 1 and A2 = 1? Likewise for any other combinations in A1 and A2.

If the answer is yes then the table suggestion I made earlir will work.

You may have already answered this, but please remember that

The factor in A3 actually it's a number that goes into a formula. The fibers are Glass, Carbon and Aramid. The orientations are Woven roving, Unidirectional paralell to fibers and Unidirectional perpendicular. There's a 4th orientation although is not in this table.

What I'm actually trying to find is the value E(in-plane modulus), which is dependent of the fiber on cell A1, orientation A2 and value Ψ inputed in cell A3.

along with the table you provided, might not mean anything to some people.

 

[AlphaFrog]

So what I'm getting is you want to use a specific formula (not value) based on A1 and A2 and then use A3 within the looked up formula.

A1 would be fiber type (1 = Glass, 2 = Carbon, and 3 = Aramid)
A2 would be fiber orientation (1 = woven, 2 = parallel, 3 = perpendicular, 4 = other)
A3 = Ψ

So for the table you listed (glass fiber) there are three formulas in the table for "In-Plane modulus, E".
Woven roving in-plane modulus = 38000*Ψ - 5000
Parallel in-plane modulus = 46000*Ψ^2 + 7200*Ψ + 7250
Perpendicular in-plane modulus = 46800*Ψ^2 + 3900*Ψ + 12500

Is that the situation?

 

[AlphaFrog]

If my description in #8 is correct, I would suggest doing what jasonb75 described in #2 where you have a lookup table for Fiber type vs orientation. But instead of constants in the table, you have each "in-plane modulus" formula for the given fiber type-orientation. Each of the 12 formulas in the table uses A3 (Ψ) in the calculation. Then you just lookup the desired result based on A1 and A2.

=INDEX(E2:G5,MATCH(A2,D2:D5,0),MATCH(A1,E1:G1,0))