#NAME? error in formula in 03 but runs okay in excel 07
 

I have a formula that works in excel 07 version fine. I can change the cell
amounts (I$2) that the "matches" are looking at it reads the formula fine.
In excel 03 if I change the # in the cells (I$2) that the formula looks at
as the "match" I get the #NAME? error. Any idea why this works in excel 07
but not 03?

it appears it does not like the mround that is included in the formula. If I
take that out the formula works but will not round a # up if not an exact
match.

=INDEX($K$7:$Z$13,MATCH($B$1,$J$7:$J$13,0),MATCH(M ROUND
(I$2,10),$K$6:$Z$6,0))
any ideas?

6.5 MON TUE The 6.5, 66 or 43 s/b able to be changed
66 43


Below is the table that it would grab info from. 66 should round to 70 and
pick 16 as the # that goes to 6.5 and 70.
70 60 50 40
5 16 15 15 14
5.5 18 17 16 15
6.5 16 18 18 19

#NAME? error in formula in 03 but runs okay in excel 07
 

MROUND function is required Analysis ToolPak add-ins


"mrpye" wrote:

I have a formula that works in excel 07 version fine. I can change the cell
amounts (I$2) that the "matches" are looking at it reads the formula fine.
In excel 03 if I change the # in the cells (I$2) that the formula looks at
as the "match" I get the #NAME? error. Any idea why this works in excel 07
but not 03?

it appears it does not like the mround that is included in the formula. If I
take that out the formula works but will not round a # up if not an exact
match.

=INDEX($K$7:$Z$13,MATCH($B$1,$J$7:$J$13,0),MATCH(M ROUND
(I$2,10),$K$6:$Z$6,0))
any ideas?

6.5 MON TUE The 6.5, 66 or 43 s/b able to be changed
66 43


Below is the table that it would grab info from. 66 should round to 70 and
pick 16 as the # that goes to 6.5 and 70.
70 60 50 40
5 16 15 15 14
5.5 18 17 16 15
6.5 16 18 18 19

#NAME? error in formula in 03 but runs okay in excel 07
 

Replace this


MROUND(I$2,10)



with this



ROUND(I$2/10,0)*10


and it will work in both versions


or install ATP, however in general it's a good idea to use built in
functions when possible

--


Regards,


Peo Sjoblom

"mrpye" wrote in message
...
I have a formula that works in excel 07 version fine. I can change the cell
amounts (I$2) that the "matches" are looking at it reads the formula fine.
In excel 03 if I change the # in the cells (I$2) that the formula looks at
as the "match" I get the #NAME? error. Any idea why this works in excel 07
but not 03?

it appears it does not like the mround that is included in the formula. If
I
take that out the formula works but will not round a # up if not an exact
match.

=INDEX($K$7:$Z$13,MATCH($B$1,$J$7:$J$13,0),MATCH(M ROUND
(I$2,10),$K$6:$Z$6,0))
any ideas?

6.5 MON TUE The 6.5, 66 or 43 s/b able to be changed
66 43


Below is the table that it would grab info from. 66 should round to 70 and
pick 16 as the # that goes to 6.5 and 70.
70 60 50 40
5 16 15 15 14
5.5 18 17 16 15
6.5 16 18 18 19

#NAME? error in formula in 03 but runs okay in excel 07
 

If you look up MROUND in Excel help it will tell you that you need to enable
the Analysis ToolPak.

Always worth trying Excel help if you need information.
--
David Biddulph

"mrpye" wrote in message
...
I have a formula that works in excel 07 version fine. I can change the cell
amounts (I$2) that the "matches" are looking at it reads the formula fine.
In excel 03 if I change the # in the cells (I$2) that the formula looks at
as the "match" I get the #NAME? error. Any idea why this works in excel 07
but not 03?

it appears it does not like the mround that is included in the formula. If
I
take that out the formula works but will not round a # up if not an exact
match.

=INDEX($K$7:$Z$13,MATCH($B$1,$J$7:$J$13,0),MATCH(M ROUND
(I$2,10),$K$6:$Z$6,0))
any ideas?

6.5 MON TUE The 6.5, 66 or 43 s/b able to be changed
66 43


Below is the table that it would grab info from. 66 should round to 70 and
pick 16 as the # that goes to 6.5 and 70.
70 60 50 40
5 16 15 15 14
5.5 18 17 16 15
6.5 16 18 18 19

#NAME? error in formula in 03 but runs okay in excel 07
 

Thanks to all of you for your help. Adding the Analysis toolpak worked!!