Adding text values and date values to get a single-digit answer

**e4excel** · 11-22-2008, 03:05 PM

Dear Forum,

I am currently doing a study on Birth Dates & Name
i.e.
Birth DayBirth MonthBirth Year24Apr1973to arrive at a single digit answer which is the Main number..

Similarly, on the same principle want to know the summation for Names..
i.e. FNameMNameLName
SACHINRAMESHTENDULKAR=1+1+3+8+9+5=9+1+4+5+1+8=2+5+5+4+3+3+2+1+9272834

The values for each ALphabet are given on the left-hand side of the sheet and the values can be changed so the code should be based on dynamic Alphabet values.

**martindwilson** · 11-22-2008, 04:22 PM

how to do this was posted here
http://www.excelforum.com/excel-work...arry-over.html
but its hard to split text so i'd recommend this aproach one letter in each cell
oh i hid rows and columns so you'll have to unhide them to see formula!

**daddylonglegs** · 11-22-2008, 04:53 PM

Hello Martin,

I see you have this formula for A7

=IF(ISNA(A19 - FLOOR(A19 - 1, 9)),"",A19 - FLOOR(A19 - 1, 9))

Why ISNA? If sum in A19 is zero you'll get a #NUM! error so you might want that to be ISERR.......or you can use this formula

=IF(A19,MOD(A19-1,9)+1,"")

**martindwilson** · 11-22-2008, 05:03 PM

correct! it was i had n/a in there before i changed the sum to give zero rather than n/a if it wasnt in the look up never thought to change it ! or i just had a rush of blood to the head

thanks for spotting it heres v2!!

**JBeaucaire** · 11-22-2008, 05:14 PM

Similar idea in the OPs original format

I was able to duplicate the three summation fields, and I was even able to eliminate Summation I by merging it INTO the formula for summation II, but I wasn't able to merge that BIG formula into the final digit number, just get an unspecified error.

Would love to know if the work of all three cells can be merged into a single cell.

**martindwilson** · 11-22-2008, 06:40 PM

i had a little think and came up with this its very simple and allows you to type whole name in a1,with or without spaces (up to 50 characters including spaces) you could extend it if required and gives result in b1 you could reduce this further by omiting row 19 and in a20 across put

Please Login or Register  to view this content.

**protonLeah** · 11-22-2008, 07:50 PM

Here's a UDF:

Please Login or Register  to view this content.

**e4excel** · 11-23-2008, 06:00 AM

Dear Everyone,,

I got to learn a lot from the responses and the links...
Ex:
<=ROW(INDIRECT("1:"& LEN(integer))),1))>

< I made this formula to add the digits of an integer:
=SUM(--MID(<integer>,ROW(INDIRECT("1:"& LEN(<integer>))),1))

You can copy it along a row or column to iterate.

http://www.excelforum.com/excel-work...arry-over.html
>

I did not quite understand it but I feel this is used for iteration..Please anyone can explain the above formula...

I tried understanding more about the FLOOR function but did not quite understand that...

However I was looking for a single formula if possible for both the Birthdate Sheet and the Name Sheet..

The Alphabets and Its values can be defined in Name Define but having sperate cells..I dont want to reserve additional cells for seperating/Dissecting the Name as I would be having a list of Names in the Column B ....

So is there a way out?

**martindwilson** · 11-23-2008, 07:18 AM

1.er dont use
=IF(ISNA(LOOKUP(MID($A$1,COLUMN(),1),$A$26:$A$51,$B$26:$B$51)),0,(LOOKUP(MID($A$1,COLUMN(),1),$A$26:$A$51,$B$26:$B$51)))
it creates a circular refrence.

2.whats wrong with solutions offered. they work to a varying degree, take them away and play with them. sorry I don't think you are really trying.!

**e4excel** · 11-23-2008, 07:47 AM

Originally Posted by martindwilson

2.whats wrong with solutions offered. they work to a varying degree, take them away and play with them. sorry I don't think you are really trying.!

I applaude the efforts put in by all the Gurus..
What I meant was to not have any reservations for storing each Alphabet for a Complete Name seperately...

The solutions are working, no denying that, however I just wanted to avoid the reservation of cells..if anyone could possibly come up with a single cell formula it will be really wonderful...

Please dont misunderstand me Martin....

**daddylonglegs** · 11-23-2008, 07:57 AM

Originally Posted by e4excel

I tried understanding more about the FLOOR function but did not quite understand that...

I you have a number in A1, e.g. 234569 then this formula will "reduce" that number to a single digit,

=MOD(A1-1,9)+1

e.g. for my example

2+3+4+5+6+9 = 29
2+9 = 11
1+1 = 2

MOD, however doesn't work with numbers equal to or greater than 1207959553 so the FLOOR version was a way to get round that, so this formula will do the same as the MOD version above, but it also works on larger numbers

=A1-FLOOR(A1-1,9)

**e4excel** · 11-23-2008, 01:20 PM

Please Login or Register  to view this content.

Dear Daddy...
Can u suggest a single cell formula.

**daddylonglegs** · 11-23-2008, 01:50 PM

Originally Posted by e4excel

Dear Daddy...
Can u suggest a single cell formula.

This just combines suggestions made above.....

Assuming you have your table of "Alphabets" and corresponding values in D2:E27 then this formula in B2 will give the single digit result, given a name in A2. Name can contain either upper or lower case letters, spaces are ignored

=MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1),D$2:E$27)+0)-1,9)+1

Of course you could "hardcode" the table values into the formula.......

**e4excel** · 11-24-2008, 12:53 AM

Originally Posted by daddylonglegs

This just combines suggestions made above.....

=MOD(SUMPRODUCT(LOOKUP(MID(SUBSTITUTE(A2," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A2," ","")))),1),D$2:E$27)+0)-1,9)+1

Of course you could "hardcode" the table values into the formula.......

Just making a small change by using a Name Define for Alphabets....

HTML Code:

Thanks each and everyone for all your help....
You people are truly amazing....