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:
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
Re: 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
--
--
HT
Bo
(there's no email, no snail mail, but somewhere should be gmail in my addy
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, et
:
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 tha
you have chosen to have it displayed
-
David Biddulph
You could fudge
You could fudge i
=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(A1))*2
--
--
HT
Bo
(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/tutorial...f-1716445b26bc/sending-smtp-email-from-w.aspx