Nested If Statement "FALSE" Return

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table set up in excel as follows:

Town 1 2 3 4
Building 1 $12MM $12MM $8.5MM $8.5
Type 2 $10MM $10MM $6.5MM $6.5MM
3 $7.5MM $7.5MM $5.0MM $5.0MM
4 $3.0MM $3.0MM $1.0MM $1.0MM
5 $1.5MM $1.5MM $500M $500M

I've created the following formula where F25 is "town" and F24 is "building
type.

"=INDEX($Z$24:$AJ$35,MATCH(F25,$Z$24:$Z$35,),MATCHF24,$Z$24:$AJ$24,))".

I need to add a third criteria - F23 - which is "quality" - denoted by 2, 3
or 4. If it is 2, the formula noted above would work. However, if it is 3
or 4, the values in the table would change; for example, if the "quality" was
3, the "town" is 1 and the "building" is 1, the value returned would be $10.5
MM. If it was 4, the value returned would be $5MM.

I have created two additional tables that mirror the table noted above with
the exception that the values shown in each column are different; I've copied
the formula noted above into the new tables, amending the cell references to
that table (ie. @index(X45:AJ57, MATCH(F25,X45:Z57,) etc., etc. I've then
set up an @if statement which reads
"=IF(F23=2,Z37),=IF(f23=3,Z59),=if(f23=4,Z81)))". If I type in 2 in the
cell, the value returns. If I type in 3, I get "FALSE". The same with 4.
I'm stuck. I know it has something to do with the fact that there is nothing
pointing to the other two tables in the IF statement. Do I name the tables -
eg. RG2, RG3, RG4? If that's the route, can I use an IF statement? If so,
how should it read?
IF(F23=2,RG2,Z37),=IF(F23=3,RG2,Z62),IF(f23=4,RG4,Z83)))? That doesn't seem
to work - I get "your formula contains an error - check Excel Help". Help
doesn't "help" in this case. I tried to jerry rig the @index formula to
include the third criteria, but I got the FALSE return as well. I tried to
create one table with all values, but it's too big and confusing.

I would really appreciate any help you can provide. I'm sorry this is so
long winded, but I've tried to include as much detail as I could to outline
the problem.

Thanks much.

Marg
 
I do not have time to test this but here is a start
You have three tables named RG2, RG3, RG4 (you have named them with Excel)
Enter the table to use in E25 (for example type: RG3)
Change formula
"=INDEX($Z$24:$AJ$35,MATCH(F25,$Z$24:$Z$35,),MATCHF24,$Z$24:$AJ$24,))".

to read "=INDEX(indirect(E25)
,MATCH(F25,indirect(E25),),MATCHF24,indirect(E25),))".

best wishes
 
Experiment with having building type in col A and the quality indicator in
col B, along the lines of

A B
1 2
1 3
1 4
2 2
2 3
2 4

This would give you one table with 3 times as many rows - since you said you
have three separate quality indicators.

To find the correct row enter the array formula (committed by pressing
Ctrl-Shift-Enter) assuming building type in A1 and quality in B1

=match(A1&B1,A4:A15&B4:B15,0)

Just combine this with the match() you are using to get the correct town and
build the INDEX() arguments with those values
 
Thanks very much for your quick response, Bernard. I'm having some problems
getting the formula to work, but it's more a "user" issue than it is the
formula. I'll keep plugging away - I'm determined to get it to work!
 
Thanks Duke. I like your suggestion. I've set up the table as suggested;
having a bit of a problem getting the formula set up as I've not used an
array formula before. Will try to figure it out - I'm halfway there!

Cheers.
 
Hi Duke:
As suggested, I've set up the table as follows:

Const Grade Town Grade
1 2 3 4
2 2 $16,000,000 $16,000,000 $16,000,000
2 3 $12,000,000 $12,000,000 $12,000,000
2 4 $4,000,000 $4,000,000 $4,000,000
3 2 $12,000,000 $12,000,000 $12,000,000
3 3 $7,500,000 $7,500,000 $7,500,000
3 4 $3,500,000 $3,500,000 $3,500,000
4 2 $12,000,000 $12,000,000 $12,000,000
4 3 $7,500,000 $7,500,000 $7,500,000
4 4 $3,500,000 $3,500,000 $3,500,000

F23 = Grade
F24 = Town Grade
F25 = Construction

I've created two formula;
=INDEX($Z$24:$AJ$39,MATCH(F25,$Z$24:$Z$34,),MATCH(F24,$Z$24:$Z$39,MATCH(F23,$AA$24:$AA$39,)))
- and =INDEXZ24:AJ39,MATCHF23&F25,Z24:Z39&AA24:AA39,MATCH(F24,Z24:AJ39,)))
- neither work. One first results in an #REF! the other #VALUE!. My
understanding of formula is very basic; I've tried to figure out by examining
the structure to see where I've gone wrong but it's not apparent, to me,
anyway. I've used Excel help to see if I can correct these errors, but I
don't understand enough about the features to determine where I've gone
wrong. As mentioned, if I input a 3 into F23, a 4 into F24 and a 2 into F25,
I should get a return of $12,000,000. If I input a 4 into F23, a 2 into F24
and a 4 into F25, I should get a return of $3,500,000. It's not happening.
Can you suggest where I've gone astray?

Thanks much!
 
Hi Margie -

Try this formula - I think you got a few of the comparisons wrong in your
version:

=INDEX($AB$25:$AD$33,MATCH(F25&F23,$Z$25:$Z$33&AA25:AA33,0),MATCH(F24,AB24:AD24))

Remember to enter it with Ctrl-Shift-Enter
 
Hi Duke:

Thanks for your help. It's not working - I'm getting a #value error now
which is way better than before. I'm determined to figure out what I've done
wrong - don't want to bug you again.

Have a good weekend. Cheers.
 
Value errors are derived either from calculating with text or applying a
formula over a range that already contains value error(s).

if A1:A4 holds


1
2
#VALUE!
3


=SUM(A1:A4)

will return a value error


so would

="a"+2



Are there any errors in your data?
 
Back
Top