Display text depending on values of 3 different cells?

  • Thread starter Thread starter henrat
  • Start date Start date
H

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....?

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

Thanks greatly in advance!

Henry
 
Try this:

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

Does that help?

Ron
 
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
 
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
 
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....?

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

Thanks greatly in advance!

Henry
Click to expand...

Richard Buttrey
__
 
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
 
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
 
Back
Top