calculating a formula using all the numbers in a cell...

  • Thread starter Thread starter Gbiwan
  • Start date Start date
G

Gbiwan

HI ALL!

987-654-321 is a number in cell a1. (I've cell formatted it to read
000-000-000 if that matters)

Now what I need to do is to prove the number entered is valid. To do this,
I need to take the first digit add it to 2* the second digit... add the
sum's 1&2 digits together... add it to the 3rd digit... add that sum
together... and so on and so on...

So the question is... how do I write a formula that allows me to reference
the digit I need?

Any ideas? It's probably easy... but I'm a little lost...any help would be
greatly appreciated!

TTFN
Greg
 
Hi Greg,

Try

=SUM(--(MID(A1,ROW(INDIRECT("1:3")),1)))+SUM(--(MID(A1,ROW(INDIRECT("5:7")),
1)))+SUM(--(MID(A1,ROW(INDIRECT("9:11")),1)))

it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
HI ALL!

987-654-321 is a number in cell a1. (I've cell formatted it to read
000-000-000 if that matters)

Now what I need to do is to prove the number entered is valid. To do this,
I need to take the first digit add it to 2* the second digit... add the
sum's 1&2 digits together... add it to the 3rd digit... add that sum
together... and so on and so on...

So the question is... how do I write a formula that allows me to reference
the digit I need?

Any ideas? It's probably easy... but I'm a little lost...any help would be
greatly appreciated!
Click to expand...

Your description is not clear to me.

1. What exactly is in A1? Is it 987654321 with a format code of
"000-000-000"? Or is it the string 987-654-321?

2. Is the second digit the only one that gets multiplied by its position?

4. Are you counting digits from left to right, or from right to left. In
other words, is '9' the first digit, or is it '1'?

5. Do the hyphens (dashes) count as a digit position?




--ron
 
Sorry can't seem to get the answer right... says that I don't have enough
arguments... any ideas?

Thanks for any help!
Greg
 
I must be really missing something... can't get any of your suggestions to
work... any ideas on what I'm missing?

Thanks for all the responses! Just maybe need babysteps?

Greg
 
How does it not work, what do you get?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry Bob!

Not ignoring you! Just got another answer that let me know what I was
missing and then I was off here for a while...

Thanks for the response!

TTFN
Greg
 
Back
Top