How can I complete this Array Formula in Excel 2000

[gahill]

Hi:
Cells c17 through c20 are formatted as number with 2 decimals.
c17=16,773.86
c18=c17*0.009
c19=0
c20=c17-c18+c19
The answer in c20 is 16,622.90 and needs to be changed to the value of 1662290 so the array can sum the digits.
Using edit copy,Paste Special,Values changes c20 to 16622.89526
If I physically type this number(1662290) in cell c20 in place of the formula the array works, otherwise I get #Value.
Here is the Array Formula:
{=sum(mid(c20,row(indirect("1:"&len(c20))),1)*1)+len(c20)}
Thank you
Gary W. Hill

 

[KenWright]

Either change the formula in C20 to this:-

=ROUND((C17-C18+C19)*100,0)

or change your array to this:-

=SUM(MID(ROUND(C20*100,0),ROW(INDIRECT("1:"&LEN(ROUND(C20*100,0)))),1)*1)+LEN(ROUND(C20*100,0))

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[gahill]

Hi Ken:

Thank you very much for your help.
The second form worked, the first one didn't.
The answer from this calculation is 33.

Again Thanks!!!!

 

[KenWright]

Just checked it again and using your values as stated, and =ROUND((C17-C18+C19)*100,0) in cell C20, I get 33 using your array formula as long as it is array entered. If it was entered incorrectly it will return 8. What are you seeing??

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[gahill]

Ken:

I must have entered the first solution incorrect because I did get the answer 8.

I'll try again later, although I like the second solution the best.

Cheers

Gary

 

[KenWright]

No problem - Don't forget though, it is your array formula that needs to be array entered, not the formula I gave you for the first option, which is why i couldn't understand it not working, as in theory there was no need to touch that formula in order to implement my first option.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------