|
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 |
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 ... 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 |
Formula for each # of dollar amount due + how many digits
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 ... 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 |
Formula for each # of dollar amount due + how many digits
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 ... 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 |
Formula for each # of dollar amount due + how many digits
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 ... 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 |
verification code
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 ... 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 |
verification code
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. |
verification code
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. |
| All times are GMT +1. The time now is 06:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com