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

[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

[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 wrote:
> 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]

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





On Tue, 22 Nov 2005 11:17:10 -0600, henrat
<[email protected]> wrote:

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