# Solved Formula in excel 2007

April 29, 2012 at 12:37:43
Specs: Windows Vista
 I am trying to find a formula that replaces the numbers no matter what number it may be separated by a comma that will multiply by 4. That probably made no sense!Here is an example of what I am trying to do:say in Cell A1 I have 1,23,4,5 I would like it to CALCULATE in Cell A2 as totaling 16.so basically its adding 4 (regardless of what the number is followed by the comma. Its all increments of 4say 1,2,3 =12say 13,23,28,30 =16say 2,6 =8say 2 =4basically the numbers are referring to dates such as May 1,23,30 so 1 representing 4 units 23 representing another 4 units 30 representing 4 units therefor 4+4+4 = 12units each day represents 1 hour of services which is equivalent to 4 units.I hope that makes sense? If you can help me I greatly appreciate you! I have tried everything I can think of its probably a very simple formula and i am complicating the calculation!Thank you :)Cynn

See More: Formula in excel 2007

#1
April 29, 2012 at 15:11:48
 Try this:=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4)+4MIKEhttp://www.skeptic.com/

Report •

#2
April 29, 2012 at 15:21:36
 Dear Mike,THANK YOU THANK YOU THANK YOU!!! Thats it! I truly appreciate you and all your hard work! You Rock!!!!Thanks again:))))Cynn

Report •

#3
May 6, 2012 at 08:57:45
 Hello,I was wondering if you might be able to help me with one more thing regarding this formula?First, the formula works great! My question is how would I get it to show in the formula column as a "-" or just left blank? Right now It always shows a 4 in that formula column and I would like for it show nothing until I start putting in the dates 1,2,23 etc.I hope this made sense maybe?Can you help me once again? I appreciate it and all your help always!Thank you Cynn

Report •

Related Solutions

#4
May 6, 2012 at 09:50:39
 Hello,I was wondering if you might be able to help me with one more thing regarding this formula?First, the formula works great! My question is how would I get it to show in the formula column as a "-" or just left blank? Right now It always shows a 4 in that formula column and I would like for it show nothing until I start putting in the dates 1,2,23 etc.I hope this made sense maybe?Can you help me once again? I appreciate it and all your help always!Thank you Cynn

Report •

#5
May 6, 2012 at 10:04:43
 Just wrap it in an IF() statement:=IF(A1="","",SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4)+4)One other caveat you should be aware of:The formula works by counting the number of commas in the stringso if you have a trailing comma, like:13,23,28,30, <-- trailing commayou will get an incorrect answer.MIKEhttp://www.skeptic.com/

Report •

#6
May 6, 2012 at 10:09:06
 If the trailing comma is a concern then you could always do something like:=IF(A1="","",IF(RIGHT(A1,1)=",",SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4),SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4)+4))MIKEhttp://www.skeptic.com/

Report •

#7
May 6, 2012 at 19:54:37
 Once again you have solved my problem! I cant thank you enough! I truly appreciate all your time and effort put forth!!! You are amazing!Thank you again!Sincerely,Cynthia

Report •