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

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


"Louise" wrote:

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

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.


Louise wrote:

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

--

Dave Peterson

Elkar:

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

thank you it works great.


"Elkar" wrote:

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


"Louise" wrote:

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

Dave:

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

thank you
Louise

"Dave Peterson" wrote:

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.


Louise wrote:

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

--

Dave Peterson