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

[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

 

[Ken Wright]

=--MID(A1,3) will return the 3rd digit in a number from cell A1

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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!

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

 

[Gbiwan]

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

Thanks for any help!
Greg

 

[Gbiwan]

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

 

[Frank Kabel]

Hi Ken
you probably meant
--MID(A1,3,1)

 

[Gbiwan]

That works!

Thanks Frank!

TTFN
Greg

Hi Ken
you probably meant
--MID(A1,3,1)

 

[Ken Wright]

Oops - Cheers Frank

 

[Bob Phillips]

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)

 

[Gbiwan]

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