IF Statement not Displaying FILLDOWN results Automatically

I'm using MS XL 2000


App.Range("AQ2").Formula =3D "=3DIF(AT2 =3D 500,MROUND(AI2,500),IF(AT2 =3D
5,MROUND(AI2,5),IF(AT2=3D10,MROU=ADND(AI2,10),IF(AT2 =3D
25,MROUND(AI2,25),IF(AT2 =3D 100,MROUND(AI2,100),IF(AT2 =3D
200,MROUND(AI2,200),IF(AT2 =3D
364,MROUND(AI2,364),ROUNDDOWN(=ADAI2,0))))))))"
App.Range("AQ2:AQ" + Total).FillDown


'where TOTAL =3D record count of a query


I get #Name? in the cells that I want to be calculated.


My formula doesn't fill correctly. I've tried switching the automatic
and manual and iteration and calculate before saving and they do not
work. I've also tried formatting the number. Any ideas?


Reply





keepITcool Aug 11, 11:28 am show options

Newsgroups: microsoft.public.excel.programming
From: "keepITcool" <[email protected]> - Find messages by this
author
Date: Thu, 11 Aug 2005 08:28:46 -0700
Local: Thurs, Aug 11 2005 11:28 am
Subject: Re: Only a genius can help with this 7 Nested If that doesn't
fill down
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

shorten that to:


=3DIF(ISERROR(MATCH(AT2,{0;5;10;=AD25;100;200;364},0)),
ROUNDDOWN(AI2,0),MROUND(AI2,AT=AD2))


Also note that MROUND is a function from
the Analysis Toolpak . (hence the NAME error?)


If that is a problem: try Floor or Ceiling instead.
but beware they dont like negative amounts.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


CondtllyFrmttd wrote :



- Hide quoted text -
- Show quoted text -

> "=3DIF(AT2 =3D 500,MROUND(AI2,500),IF(AT2 =3D
> 5,MROUND(AI2,5),IF(AT2=3D10,MROU=ADND(AI2,10),IF(AT2 =3D
> 25,MROUND(AI2,25),IF(AT2 =3D 100,MROUND(AI2,100),IF(AT2 =3D
> 200,MROUND(AI2,200),IF(AT2 =3D
> 364,MROUND(AI2,364),ROUNDDOWN(=ADAI2,0))))))))"


Reply





CondtllyFrmttd Aug 11, 1:10 pm show options

Newsgroups: microsoft.public.excel.programming
From: "CondtllyFrmttd" <[email protected]> - Find messages
by this author
Date: 11 Aug 2005 10:10:37 -0700
Local: Thurs, Aug 11 2005 1:10 pm
Subject: Re: Only a genius can help with this 7 Nested If that doesn't
fill down
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Both my formula and yours work. THe only catch is that I have to
manually put my cursor in the actual formula and hit enter. Why the
hell does it do this??!?!?!?!?!? I still get #Name?=20


Reply

Hi,
Could it be coming from the Mround function?
The MRound function is part of the Analysis Toolpack addin, so if the addin
is not loaded or not installed, the function will return #Name.
-To load it, goto to menu Tools > AddIn and check the Analysis Toolpack addin
-If it does not appear in the addin list, install it from your MS Office CD.
--
Regards,
Sébastien


"CondtllyFrmttd" wrote:

>
> I'm using MS XL 2000
>
>
> App.Range("AQ2").Formula = "=IF(AT2 = 500,MROUND(AI2,500),IF(AT2 =
> 5,MROUND(AI2,5),IF(AT2=10,MROUÂ*ND(AI2,10),IF(AT2 =
> 25,MROUND(AI2,25),IF(AT2 = 100,MROUND(AI2,100),IF(AT2 =
> 200,MROUND(AI2,200),IF(AT2 =
> 364,MROUND(AI2,364),ROUNDDOWN(Â*AI2,0))))))))"
> App.Range("AQ2:AQ" + Total).FillDown
>
>
> 'where TOTAL = record count of a query
>
>
> I get #Name? in the cells that I want to be calculated.
>
>
> My formula doesn't fill correctly. I've tried switching the automatic
> and manual and iteration and calculate before saving and they do not
> work. I've also tried formatting the number. Any ideas?
>
>
> Reply
>
>
>
>
>
> keepITcool Aug 11, 11:28 am show options
>
> Newsgroups: microsoft.public.excel.programming
> From: "keepITcool" <[email protected]> - Find messages by this
> author
> Date: Thu, 11 Aug 2005 08:28:46 -0700
> Local: Thurs, Aug 11 2005 11:28 am
> Subject: Re: Only a genius can help with this 7 Nested If that doesn't
> fill down
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse
>
> shorten that to:
>
>
> =IF(ISERROR(MATCH(AT2,{0;5;10;Â*25;100;200;364},0)),
> ROUNDDOWN(AI2,0),MROUND(AI2,ATÂ*2))
>
>
> Also note that MROUND is a function from
> the Analysis Toolpak . (hence the NAME error?)
>
>
> If that is a problem: try Floor or Ceiling instead.
> but beware they dont like negative amounts.
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> CondtllyFrmttd wrote :
>
>
>
> - Hide quoted text -
> - Show quoted text -
>
> > "=IF(AT2 = 500,MROUND(AI2,500),IF(AT2 =
> > 5,MROUND(AI2,5),IF(AT2=10,MROUÂ*ND(AI2,10),IF(AT2 =
> > 25,MROUND(AI2,25),IF(AT2 = 100,MROUND(AI2,100),IF(AT2 =
> > 200,MROUND(AI2,200),IF(AT2 =
> > 364,MROUND(AI2,364),ROUNDDOWN(Â*AI2,0))))))))"
>
>
> Reply
>
>
>
>
>
> CondtllyFrmttd Aug 11, 1:10 pm show options
>
> Newsgroups: microsoft.public.excel.programming
> From: "CondtllyFrmttd" <[email protected]> - Find messages
> by this author
> Date: 11 Aug 2005 10:10:37 -0700
> Local: Thurs, Aug 11 2005 1:10 pm
> Subject: Re: Only a genius can help with this 7 Nested If that doesn't
> fill down
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
>
> Both my formula and yours work. THe only catch is that I have to
> manually put my cursor in the actual formula and hit enter. Why the
> hell does it do this??!?!?!?!?!? I still get #Name?
>
>
> Reply
>
>

I do not have a clue!!! The tool pak is not part of the problem.


sebastienm wrote:
> Hi,
> Could it be coming from the Mround function?
> The MRound function is part of the Analysis Toolpack addin, so if the add=
in
> is not loaded or not installed, the function will return #Name.
> -To load it, goto to menu Tools > AddIn and check the Analysis Toolpack a=
ddin
> -If it does not appear in the addin list, install it from your MS Office =
CD.
> --
> Regards,
> S=E9bastien
>
>
> "CondtllyFrmttd" wrote:
>
> >
> > I'm using MS XL 2000
> >
> >
> > App.Range("AQ2").Formula =3D "=3DIF(AT2 =3D 500,MROUND(AI2,500),IF(AT2 =
=3D
> > 5,MROUND(AI2,5),IF(AT2=3D10,MROU=ADND(AI2,10),IF(AT2 =3D
> > 25,MROUND(AI2,25),IF(AT2 =3D 100,MROUND(AI2,100),IF(AT2 =3D
> > 200,MROUND(AI2,200),IF(AT2 =3D
> > 364,MROUND(AI2,364),ROUNDDOWN(=ADAI2,0))))))))"
> > App.Range("AQ2:AQ" + Total).FillDown
> >
> >
> > 'where TOTAL =3D record count of a query
> >
> >
> > I get #Name? in the cells that I want to be calculated.
> >
> >
> > My formula doesn't fill correctly. I've tried switching the automatic
> > and manual and iteration and calculate before saving and they do not
> > work. I've also tried formatting the number. Any ideas?
> >
> >
> > Reply
> >
> >
> >
> >
> >
> > keepITcool Aug 11, 11:28 am show options
> >
> > Newsgroups: microsoft.public.excel.programming
> > From: "keepITcool" <[email protected]> - Find messages by this
> > author
> > Date: Thu, 11 Aug 2005 08:28:46 -0700
> > Local: Thurs, Aug 11 2005 11:28 am
> > Subject: Re: Only a genius can help with this 7 Nested If that doesn't
> > fill down
> > Reply | Reply to Author | Forward | Print | Individual Message | Show
> > original | Report Abuse
> >
> > shorten that to:
> >
> >
> > =3DIF(ISERROR(MATCH(AT2,{0;5;10;=AD25;100;200;364},0)),
> > ROUNDDOWN(AI2,0),MROUND(AI2,AT=AD2))
> >
> >
> > Also note that MROUND is a function from
> > the Analysis Toolpak . (hence the NAME error?)
> >
> >
> > If that is a problem: try Floor or Ceiling instead.
> > but beware they dont like negative amounts.
> >
> >
> > --
> > keepITcool
> > | www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > CondtllyFrmttd wrote :
> >
> >
> >
> > - Hide quoted text -
> > - Show quoted text -
> >
> > > "=3DIF(AT2 =3D 500,MROUND(AI2,500),IF(AT2 =3D
> > > 5,MROUND(AI2,5),IF(AT2=3D10,MROU=ADND(AI2,10),IF(AT2 =3D
> > > 25,MROUND(AI2,25),IF(AT2 =3D 100,MROUND(AI2,100),IF(AT2 =3D
> > > 200,MROUND(AI2,200),IF(AT2 =3D
> > > 364,MROUND(AI2,364),ROUNDDOWN(=ADAI2,0))))))))"
> >
> >
> > Reply
> >
> >
> >
> >
> >
> > CondtllyFrmttd Aug 11, 1:10 pm show options
> >
> > Newsgroups: microsoft.public.excel.programming
> > From: "CondtllyFrmttd" <[email protected]> - Find messages
> > by this author
> > Date: 11 Aug 2005 10:10:37 -0700
> > Local: Thurs, Aug 11 2005 1:10 pm
> > Subject: Re: Only a genius can help with this 7 Nested If that doesn't
> > fill down
> > Reply | Reply to Author | Forward | Print | Individual Message | Show
> > original | Remove | Report Abuse
> >
> > Both my formula and yours work. THe only catch is that I have to
> > manually put my cursor in the actual formula and hit enter. Why the
> > hell does it do this??!?!?!?!?!? I still get #Name?
> >=20
> >=20
> > Reply
> >=20
> >

Your formula (placed in cell A1) works for me (with the Analysis Toolpack
loaded of course). Sorry no idea.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>