I use excel to track my training and I’ve got a problem with an addition calculation.
If anyone knows excel well, I’d could really use some help before it drives me nuts!
I’m simply trying to add 4 cells together, but the result returned is not correct.
I can send you a copy if you think you could help.
Thanks!
Type this into the field where you want the result:
= cell1 + cell2 + cell3 + cell4
However, instead of typing “cell1” “cell2” etc, type in the label of the cell like A3, so it would look more like … = A3 + A4 + A5 + A6
Thanks, I’ll try that.
Thanks, I’ll try that.
No, still get the same result.
What are the values of the 4 cells and what’s the result that you are getting?
Also check the format of the result cell. Right click on it, choose “Format”, I think its the 1st or 2nd tab that indicates how the cell should be formatted. Choose one of the number formats, if not already chosen.
Check the cells’ format. Under “Numbers”, see if the category is accidentally set to something like “fraction” or “date” (or “text”) or something else that makes Excel interpret the cell’s contents differently from what you intended.
HTH
Cheers,
malte
try =SUM(A1+A2+A3+A4)
.
10:10:00 + 6:55:00 + 6:25:30 + 7:27:06
No matter how I try to get excel to add it up it gives me 6:57:36.
Speaking of… does anyone have a nicely designed blank template with the calculations already in there?
Yep, looks like a formatting issue. Highlight all (or each one) and change format of each to Custom and then choose h:mm:ss. It doesn’t know your cells are in terms of time.
click into one of your fields. Go to menu/format/cells look at the category. It should be set to custom on the left hand pane and the right side should show h:m:ss. I put you numbers in and the result came back 30:57:36
10:10:00 + 6:55:00 + 6:25:30 + 7:27:06
No matter how I try to get excel to add it up it gives me 6:57:36.
Your cells are formatted to display clock time. If you start at 10:10 AM and the indicated lengths of time pass, you end up at 6:57 AM next day 
Just change the settings in the Format menue.
Cheers,
malte
Thanks,
Format is set to h:mm:ss and I’m still getting the same result!
What should the correct format be?
You checked/changed the format for all 5 cells?
Yes, all the same.
custom, h:mm:ss
What version of excel, what platform and when was last reboot?
Copy/paste the formula that you have in your result cell in your response here, so we can take a look.
sorry… misunderstood the problem.
Excel is horrible(it makes too many decisions for you) with calculating dates and times… i generally avoid using time formats and just do the calculations
Something like this:
A B C D
10:10:00 =HOUR(A1) =MINUTE(A1) =SECOND(A1)
6:55:00 =HOUR(B1) =MINUTE(B1) =SECOND(B1)
6:25:30 =HOUR(C1) =MINUTE(C1) =SECOND(C1)
7:27:06 =HOUR(D1) =MINUTE(D1) =SECOND(D1)
=SUM(B1:B4) =SUM(C1:C4) =SUM(D1:D4)
total
=B7&“:”&C7&“:”&D7 =B5+TRUNC(C5/60,0) =C5-60TRUNC(C5/60,0)+TRUNC(D5/60,0) =D5-60TRUNC(D5/60,0)
This gives me 30:57:36
wow that really didn’t format very well… if you want me to mail you a sample file, let me know