WEIRD QUESTION

L

Louise

I have to calc every mth for the State of calif the following. you know how
govn offices are?????

if the amount is 56,995.87 I have to calc the following

5+6+9+9+5+8+7 = 49 + 7 =63

h=the $56K number varies every time it is calc. I have been using
spreadsheets since lotus 1A but I am at a loss on how to do this. Is anyone
up for this?

Don't you just love government offices.

Louise
 
E

Elkar

I'm not sure how 49 + 7 = 63, but perhaps something like this array formula
might help? This actually results in 56 (49+7), but maybe this will at least
point you in the right direction.

=SUM(VALUE(RIGHT(INT($A$1/{10000,1000,100,10,1,0.1,0.01}),1)))+7

Array formulas are entered with CTRL-SHIFT-ENTER instead of just Enter. You
should see { } surrounding the formula if done properly.

This formula will work for numbers up to 99,999.99. If you need to go
larger, then just add 100000, 1000000 etc...

HTH,
Elkar
 
D

Dave Peterson

Bob Phillips just posted this in response to a very similar question:

=SUMPRODUCT(--(MID(SUBSTITUTE(A1,".",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,".","")))),1)))

I'm not sure where the 7 comes from, though.

If it's the number of digits in that value, you could include this:
-LEN(TEXT(100*A1,"0"))
at the end.
 
L

Louise

Elkar:

OK so I can't add sometimes. I am just an accountant.... ;-)

thank you it works great.
 
L

Louise

Dave:

I will try this also. The 7 is just a # that the St of CA added???

thank you
Louise
 
Top