IF, VLOOKUP & LOOKUP TABLE ON OTHER SHEET

[Serge]

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?

 

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

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

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.

Returns: 21.539294831829

Biff

 

[Max]

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.

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

 

[Serge]

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.

 

[Serge]

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

 

[Serge]

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

 

[Serge]

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

 

[Max]

You're welcome, Serge !

 

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

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

 

[Serge]

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

 

[Max]

Glad to hear it worked for you, Serge !
Thanks for the feedback ..

 

[David Biddulph]

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.

 

[Serge]

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

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.

 

[David Biddulph]

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.

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?

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.

 

[Serge]

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

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.

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?

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.

 

[Serge]

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

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.

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?

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.