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 4
say 1,2,3 =12
say 13,23,28,30 =16
say 2,6 =8
say 2 =4
basically 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

Report •


#1
April 29, 2012 at 15:11:48
✔ Best Answer
Try this:

=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4)+4

MIKE

http://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 string
so if you have a trailing comma, like:

13,23,28,30, <-- trailing comma

you will get an incorrect answer.

MIKE

http://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))

MIKE

http://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 •

Ask Question