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" - Find messages by this
author
Date: Thu, 11 Aug 2005 08:28:46 -0700
Local: Thurs, Aug 11 2005 11:28 am
Subject: 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" - Find messages
by this author
Date: 11 Aug 2005 10:10:37 -0700
Local: Thurs, Aug 11 2005 1:10 pm
Subject: 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

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" - Find messages by this
author
Date: Thu, 11 Aug 2005 08:28:46 -0700
Local: Thurs, Aug 11 2005 11:28 am
Subject: 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" - Find messages
by this author
Date: 11 Aug 2005 10:10:37 -0700
Local: Thurs, Aug 11 2005 1:10 pm
Subject: 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 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" - Find messages by this
author
Date: Thu, 11 Aug 2005 08:28:46 -0700
Local: Thurs, Aug 11 2005 11:28 am
Subject: 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" - Find messages
by this author
Date: 11 Aug 2005 10:10:37 -0700
Local: Thurs, Aug 11 2005 1:10 pm
Subject: 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

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