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?


--
Regards,

Peo Sjoblom



"Margie" wrote in message
...
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.

"Duke Carey" wrote:

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&AA2 5:AA33,0),MATCH(F24,AB24:AD24))

Remember to enter it with Ctrl-Shift-Enter

"Margie" wrote:

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,MATC H(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!


"Duke Carey" wrote:

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




"Margie" wrote:

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,),MATCH F24,$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