Display text depending on values of 3 different cells?

[henrat]

Hi guys, I'm doing an assessment for Uni which involves using Excel to
calculate various acoustic measurements of a certain room. Not sure if
anyone here knows any acoustics, but I have a formula to find every
Mode in the room:

f(nx, ny, nz) = (c/2)*SQRT((nx/Lx)^2+(ny/Ly)^2+(nz/Lz)^2)

Basically the formula relies on the dimensions of the room (Lx, Ly, Lz)
and integer multiples of them (nx, ny, nz). So I have a very long list
of different combinations or nx, ny, nz:

0, 0, 1
0, 1, 0
1, 0, 0
0, 1, 1
......
6, 7, 5 etc...

These three values are entered in separate columns.

When there are two cells containing "0"s, I would like a cell to
display the text "Axial"
When only one cell contains "0" I would like the text to read
"Tangential"
When all three cells contain a number other than "0" it should show
"Oblique"

So FINALLY my question is, how can I make a cell show one of these
words depending on what the 3 cells containing the numbers are
showing....?

Hope that makes some sense... I'm getting so confused!

Thanks greatly in advance!

Henry

 

[Ron Coderre]

Try this:

=CHOOSE(COUNTIF(A1:C1,0)+1,"Oblique","Tangential","Axial","Axial")

Does that help?

Ron

 

[Roger Govier]

Hi

Assuming your data is in columns A:C enter in D1
=CHOOSE(COUNTIF(A1:C1,"=0")+1, "","Tangential","Axial","Oblique")
and copy down column D as far as required.

Regards

Roger Govier

 

[henrat]

You are a genius!

Works perfectly, thanks very much!

Would you mind just quickly explain what it is doing please? How
exactly does it know to count the number of zeros?

Thanks
Henry

 

[Richard Buttrey]

With your data in A1:C1, in D1 enter:

=IF(COUNTIF(A1:C1,0)=2,"Axial",IF(COUNTIF(A1:C1,0)=1,"Tangential","Oblique"))

HTH

Hi guys, I'm doing an assessment for Uni which involves using Excel to
calculate various acoustic measurements of a certain room. Not sure if
anyone here knows any acoustics, but I have a formula to find every
Mode in the room:

f(nx, ny, nz) = (c/2)*SQRT((nx/Lx)^2+(ny/Ly)^2+(nz/Lz)^2)

Basically the formula relies on the dimensions of the room (Lx, Ly, Lz)
and integer multiples of them (nx, ny, nz). So I have a very long list
of different combinations or nx, ny, nz:

0, 0, 1
0, 1, 0
1, 0, 0
0, 1, 1
.....
6, 7, 5 etc...

These three values are entered in separate columns.

When there are two cells containing "0"s, I would like a cell to
display the text "Axial"
When only one cell contains "0" I would like the text to read
"Tangential"
When all three cells contain a number other than "0" it should show
"Oblique"

So FINALLY my question is, how can I make a cell show one of these
words depending on what the 3 cells containing the numbers are
showing....?

Hope that makes some sense... I'm getting so confused!

Thanks greatly in advance!

Henry

Richard Buttrey
__

 

[Ron Coderre]

Explanation:

=CHOOSE(COUNTIF(A1:C1,0)+1,"Oblique","Tangential", "Axial","Axial")

In pieces:
COUNTIF(range_of_cells,criteria_to_test_for)
Looks in a range of cells and returns the count of CELLS that match the
criteriia
In your example, it counts the number of cells in A1:C1 that are equal
to zero.


CHOOSE(IndexNumber,Value_1,Value_2,...)
Returns the nTH item in the argument list...
Where:
IndexNumber: represents "n" and is an integer greater than 0
(1,2,3...etc)
Value_1: holds the return value if the IndexNumber is 1
Value_2: holds the return value if the IndexNumber is 2
etc

In our example, I had to add 1 to the COUNTIF function may return zero
(the minimun IndexNumber value is 1)

Note: You must account for ALL possible IndexNumbers.
In our example, we must account for:
No zeros
1 zero
2 zeros
3 zeros
Consequently, we need 4 return value arguments, If we only include 3,
the function will return an error.

Does that help?

Ron

 

[henrat]

Crystal clear. It was the +1 bit that was confusing me.. but makes total
sense now.

Many thanks for your time guys! I really appreciate it!

Henry