Formula for each # of dollar amount due + how many digits

I need some help creating a function or formula in Excel for the following:

• Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Olivia" wrote in message
news
I need some help creating a function or formula in Excel for the following:

. Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

Hi,

The formula works great. Thank you. But I do have a question. The formula
does not calculate the zero's after the decimal point. For example: if the
amount is $2,564.00, then the formula should equal to 23. Instead, the
formula calculates to 21.

Does this have something to do with the way the cell (C89) is formatted? i.e
Text, number, accounting, etc


"Bob Phillips" wrote:

=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Olivia" wrote in message
news
I need some help creating a function or formula in Excel for the following:

. Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

Excel stores 2564 as 2564, not as $2564.00. $2564.00 is just the way that
you have chosen to have it displayed.
--
David Biddulph

"Olivia" wrote in message
...
Hi,

The formula works great. Thank you. But I do have a question. The formula
does not calculate the zero's after the decimal point. For example: if the
amount is $2,564.00, then the formula should equal to 23. Instead, the
formula calculates to 21.

Does this have something to do with the way the cell (C89) is formatted?
i.e
Text, number, accounting, etc


"Bob Phillips" wrote:

=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Olivia" wrote in message
news
I need some help creating a function or formula in Excel for the
following:

. Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $
amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will
automatically
populate once we enter a payment amount in a different cell.

Thanks

You could fudge it

=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),
COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(A1=INT(A 1))*2)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"David Biddulph" groups [at] biddulph.org.uk wrote in message
...
Excel stores 2564 as 2564, not as $2564.00. $2564.00 is just the way that
you have chosen to have it displayed.
--
David Biddulph

"Olivia" wrote in message
...
Hi,

The formula works great. Thank you. But I do have a question. The formula
does not calculate the zero's after the decimal point. For example: if
the
amount is $2,564.00, then the formula should equal to 23. Instead, the
formula calculates to 21.

Does this have something to do with the way the cell (C89) is formatted?
i.e
Text, number, accounting, etc


"Bob Phillips" wrote:

=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to
excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Olivia" wrote in message
news I need some help creating a function or formula in Excel for the
following:

. Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $
amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will
automatically
populate once we enter a payment amount in a different cell.

Thanks

Did you ever find a function for the verification code? I also need that.



Olivi wrote:

Formula for each # of dollar amount due + how many digits
06-Mar-08

I need some help creating a function or formula in Excel for the following:

??? Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

Previous Posts In This Thread:

On Thursday, March 06, 2008 12:34 PM
Olivi wrote:

Formula for each # of dollar amount due + how many digits
I need some help creating a function or formula in Excel for the following:

??? Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

On Thursday, March 06, 2008 2:13 PM
Bob Phillips wrote:

Formula for each # of dollar amount due + how many digits
=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Olivia" wrote in message
news
On Monday, March 10, 2008 4:38 PM
Olivi wrote:

Hi,The formula works great. Thank you. But I do have a question.
Hi,

The formula works great. Thank you. But I do have a question. The formula
does not calculate the zero's after the decimal point. For example: if the
amount is $2,564.00, then the formula should equal to 23. Instead, the
formula calculates to 21.

Does this have something to do with the way the cell (C89) is formatted? i.e
Text, number, accounting, etc


"Bob Phillips" wrote:

On Monday, March 10, 2008 4:52 PM
David Biddulph wrote:

Excel stores 2564 as 2564, not as $2564.00. $2564.
Excel stores 2564 as 2564, not as $2564.00. $2564.00 is just the way that
you have chosen to have it displayed.
--
David Biddulph

On Tuesday, March 11, 2008 8:08 AM
Bob Phillips wrote:

You could fudge
You could fudge it

=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),
COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(A1=INT(A 1))*2)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"David Biddulph" groups [at] biddulph.org.uk wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Sending SMTP email from within BizTalk Orchestration
http://www.eggheadcafe.com/tutorials...il-from-w.aspx

The following array formula (commit with CTRL+SHIFT+ENTER) will work:

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


christine mullholand wrote:
Did you ever find a function for the verification code? I also need that.



Olivi wrote:

Formula for each # of dollar amount due + how many digits
06-Mar-08

I need some help creating a function or formula in Excel for the following:

??? Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

Previous Posts In This Thread:

On Thursday, March 06, 2008 12:34 PM
Olivi wrote:

Formula for each # of dollar amount due + how many digits
I need some help creating a function or formula in Excel for the following:

??? Example: payment amount is $2,589.32
Run a tape: 2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) =
35 --- 35 is your verification code.

Is there a formula or function in excel, so that this will automatically
populate once we enter a payment amount in a different cell.

Thanks

On Thursday, March 06, 2008 2:13 PM
Bob Phillips wrote:

Formula for each # of dollar amount due + how many digits
=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

Shorter version (still array formula):

=SUM(--("0"&MID(A1,ROW(1:99),1)))+LEN(SUBSTITUTE(A1,"."," "))


Glenn wrote:
The following array formula (commit with CTRL+SHIFT+ENTER) will work:

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


christine mullholand wrote:
Did you ever find a function for the verification code? I also need
that.



Olivi wrote:

Formula for each # of dollar amount due + how many digits
06-Mar-08

I need some help creating a function or formula in Excel for the
following:

??? Example: payment amount is $2,589.32 Run a tape:
2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) = 35 --- 35 is
your verification code.
Is there a formula or function in excel, so that this will
automatically populate once we enter a payment amount in a different
cell.

Thanks

Previous Posts In This Thread:

On Thursday, March 06, 2008 12:34 PM
Olivi wrote:

Formula for each # of dollar amount due + how many digits
I need some help creating a function or formula in Excel for the
following:

??? Example: payment amount is $2,589.32 Run a tape:
2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) = 35 --- 35 is
your verification code.
Is there a formula or function in excel, so that this will
automatically populate once we enter a payment amount in a different
cell.

Thanks

On Thursday, March 06, 2008 2:13 PM
Bob Phillips wrote:

Formula for each # of dollar amount due + how many digits
=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))


which is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly
brackets), do not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to
excel 2007), but must use an explicit range.