IF, VLOOKUP & LOOKUP TABLE ON OTHER SHEET

G

Guest

The formula below works ok in the first sheet called "Right-Angled Triangle"
In T27 =IF(ISNA(VLOOKUP(S27,Z4:AA84,2,1)),"",VLOOKUP(S27,Z4:AA84,2,1)*P27)
Lookup table is Z4:AA84

But in sheet 2 called "Oblique-Angled Triangle"
a similar formula does not work.
In U48 =IF(ISNA(VLOOKUP("T48,'Right-Angled
Triangle'!",Z4:AA84,2,1)),"","VLOOKUP(T48,'Right-Angled
Triangle'!,Z4:AA84,2,1)*Q48")
I am trying to use the same table for both sheets.

also one more question if it's ok.
I'm not getting the correct answer?
In A48, B48, C48 will be user input which will be empty at the beginning.
In E48
=IF(OR(A48="",B48="",C48=""),0,A48*(SIN(RADIANS(C48)/(SIN(RADIANS(B48))))))
The answer to this formula is 21.416 which is incorrect
Oblique Triangle solution:
Side a in A48=25.0
Angle A in B48=79.94 Degrees
Angle B in C48=58.03 Degrees

Side 'b' = a x sin 'B' / sin 'A'
The calculator answer is 21.5407 which is correct.

What am I doing wrong?
 
B

Biff

Hi Serge!

Your second lookup formula has some double quotes (") and some commas (,)
that are causing the problem. Try this cleaned-up version:

=IF(ISNA(VLOOKUP(T48,'Right-Angled
Triangle'!Z4:AA84,2,1)),"",VLOOKUP(T48,'Right-Angled
Triangle'!Z4:AA84,2,1)*Q48)
=IF(OR(A48="",B48="",C48=""),0,A48*(SIN(RADIANS(C48)/(SIN(RADIANS(B48))))))
The answer to this formula is 21.416 which is incorrect
The calculator answer is 21.5407 which is correct.
Click to expand...

If the values referencd are user input and not calculated........

You have a misplaced ")" which changes the precedence of the calculation:

=IF(OR(A48="",B48="",C48=""),0,A48*(SIN(RADIANS(C48)/(SIN(RADIANS(B48))))))
The answer to this formula is 21.416 which is incorrect
Click to expand...

Returns: 21.4148138423053

Matching up the ( ) to:

=IF(OR(A48="",B48="",C48=""),0,A48*SIN(RADIANS(C48))/SIN(RADIANS(B48)))
The calculator answer is 21.5407 which is correct.
Click to expand...

Returns: 21.539294831829

Biff
 
G

Guest

Serge said:
The formula below works ok in the first sheet called "Right-Angled Triangle"
In T27 =IF(ISNA(VLOOKUP(S27,Z4:AA84,2,1)),"",
VLOOKUP(S27,Z4:AA84,2,1)*P27). Lookup table is Z4:AA84

But in sheet 2 called "Oblique-Angled Triangle"
a similar formula does not work.
In U48 =IF(ISNA(VLOOKUP("T48,'Right-Angled
Triangle'!",Z4:AA84,2,1)),"","VLOOKUP(T48,'Right-Angled
Triangle'!,Z4:AA84,2,1)*Q48")
I am trying to use the same table for both sheets.
Click to expand...

Some thoughts for the above ..

In sheet: Oblique-Angled Triangle

Try this in U48:
=IF(ISNA(VLOOKUP(T48,'Right-Angled
Triangle'!Z4:AA84,2,1)),"",VLOOKUP(T48,'Right-Angled
Triangle'!Z4:AA84,2,1)*Q48)

Alternatively, it might be easier to create a defined name for the
table_array (in sheet: Right-Angled Triangle) which could then be referred to
in any sheet in the book

One quick way* to create a defined name ..

In sheet: Right-Angled Triangle

Select Z4:AA84 (i.e. the table_array),
then click inside the namebox
(box with the drop-arrow just to the left of the formula bar)
Key-in a name, say: MyTable
then press Enter

Then in sheet: Oblique-Angled Triangle
we could put this shorter version in U48:
=IF(ISNA(VLOOKUP(T48,MyTable,2,1)),"",VLOOKUP(T48,MyTable,2,1)*Q48)

*the normal way would be via clicking Insert > Name > Define
(Options to create and delete defined names are there. Note that we can't
use the namebox to delete defined names, only to create.)


---
 
G

Guest

Hello Biff,
Its 6:15 am.just before going to work.
I tried for more than two hours last without any luck.
Thank you for your reply, I will try your version and get back to you.

Thank you very much.
Serge
ps; I would like to adopt you as a brother.
 
G

Guest

Hello Max,
Thank you very much for your input. I will try that first chance I get and
give you some feed back.
Serge
 
G

Guest

Hello Biff,
I'm back home (7pm)
I tried the "cleaned-up version" and it works fine now. Thanks again.

I'm working on another Triangle solution which I have a bit of trouble with.
In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?

Much appreciation
Serge
 
G

Guest

Hello Max,
(I'm back home, It's 7:30 pm Pacific)
I changed my formula to match yours with proper name for the TABLE and it
works great.
I did something similar not too long ago, but I could not remember I to do it.
At the age of nearly 63, sometime I forget to remember to take my memory pill.
I am very glad you could help.
Thanks
Serge
 
B

Biff

In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?
Click to expand...

Not having any luck with that one. I don't know much about trig but no
matter how I change the precedence I'm not getting anything close to 79.935.

Biff
 
G

Guest

Hello Biff,
Thank you for trying anyway.
If I get an answer I will forward it to you.
Serge
 
D

David Biddulph

Serge said:
I'm working on another Triangle solution which I have a bit of trouble
with.
In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?
Click to expand...

You need:
tan A = a x sin C / (b - (a x cos C)), noting the brackets around the b - (a
* cos C) [and of course you don't strictly need the brackets around a * cos
C].

E51=A51*SIN(RADIANS(C51))/(B51-A51*COS(RADIANS(C51)))
Your answer is =DEGREES(ATAN(E51)) which gives the 79.935 you were looking
for.
 
G

Guest

Hello David,
Thanks for your reply.
It's not working for me. I copied the formula as it was written and pasted
it E51 and the answer I got was 5.63 degrees.
Is there something I'm missing?
Serge

David Biddulph said:
Serge said:
I'm working on another Triangle solution which I have a bit of trouble
with.
In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?
Click to expand...

You need:
tan A = a x sin C / (b - (a x cos C)), noting the brackets around the b - (a
* cos C) [and of course you don't strictly need the brackets around a * cos
C].

E51=A51*SIN(RADIANS(C51))/(B51-A51*COS(RADIANS(C51)))
Your answer is =DEGREES(ATAN(E51)) which gives the 79.935 you were looking
for.
Click to expand...
 
D

David Biddulph

David Biddulph said:
Serge said:
I'm working on another Triangle solution which I have a bit of trouble
with.
In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?
Click to expand...

You need:
tan A = a x sin C / (b - (a x cos C)), noting the brackets around the b -
(a
* cos C) [and of course you don't strictly need the brackets around a *
cos
C].

E51=A51*SIN(RADIANS(C51))/(B51-A51*COS(RADIANS(C51)))
Your answer is =DEGREES(ATAN(E51)) which gives the 79.935 you were
looking
for.
Click to expand...
Click to expand...
Hello David,
Thanks for your reply.
It's not working for me. I copied the formula as it was written and pasted
it E51 and the answer I got was 5.63 degrees.
Is there something I'm missing?
Click to expand...

Please read my reply again.

What you got in E51 was *not* 5.63 degrees. It was 5.63. E51 is the value
of tan A in your explanation above.
If you want the angle A in degrees, you need =DEGREES(ATAN(E51)) which gives
79.935.
 
G

Guest

Hello David,
I finally got my head arround it. I misunderstood the first time.
It works fine now with one minor glitch. If the user input cells (A51, B51 &
C51) are empty at the beginning I get #DIV/0! in E51.
Can the formula be modified to show a blank cell or at least to show a "0"
zero.
Many thanks for your help David
Serge

David Biddulph said:
David Biddulph said:
I'm working on another Triangle solution which I have a bit of trouble
with.
In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?

You need:
tan A = a x sin C / (b - (a x cos C)), noting the brackets around the b -
(a
* cos C) [and of course you don't strictly need the brackets around a *
cos
C].

E51=A51*SIN(RADIANS(C51))/(B51-A51*COS(RADIANS(C51)))
Your answer is =DEGREES(ATAN(E51)) which gives the 79.935 you were
looking
for.
Click to expand...
Click to expand...
Hello David,
Thanks for your reply.
It's not working for me. I copied the formula as it was written and pasted
it E51 and the answer I got was 5.63 degrees.
Is there something I'm missing?
Click to expand...

Please read my reply again.

What you got in E51 was *not* 5.63 degrees. It was 5.63. E51 is the value
of tan A in your explanation above.
If you want the angle A in degrees, you need =DEGREES(ATAN(E51)) which gives
79.935.
Click to expand...
 
G

Guest

Hello David,
Just to let you know, I was able to modified your formula as follows:
In E51
=IF(OR(A51="",B51="",C51=""),0,DEGREES(ATAN(A51*SIN(RADIANS(C51))/(B51-A51*COS(RADIANS(C51))))))

Thanks again
Serge

David Biddulph said:
David Biddulph said:
I'm working on another Triangle solution which I have a bit of trouble
with.
In E51=A51*SIN(RADIANS(C51))/B51-(A51*(COS(RADIANS(C51))))
To represent: tan A = a x sin C / b - (a x cos C)
a=25.0 in A51
b=21.5407 in B51
Angle C=42.03 Degrees in C51
Answer should be 79.935 degrees.

could you help with this one as well?

You need:
tan A = a x sin C / (b - (a x cos C)), noting the brackets around the b -
(a
* cos C) [and of course you don't strictly need the brackets around a *
cos
C].

E51=A51*SIN(RADIANS(C51))/(B51-A51*COS(RADIANS(C51)))
Your answer is =DEGREES(ATAN(E51)) which gives the 79.935 you were
looking
for.
Click to expand...
Click to expand...
Hello David,
Thanks for your reply.
It's not working for me. I copied the formula as it was written and pasted
it E51 and the answer I got was 5.63 degrees.
Is there something I'm missing?
Click to expand...

Please read my reply again.

What you got in E51 was *not* 5.63 degrees. It was 5.63. E51 is the value
of tan A in your explanation above.
If you want the angle A in degrees, you need =DEGREES(ATAN(E51)) which gives
79.935.
Click to expand...
 
Top