Formula to Add Values in 2 Cells Fails But NO ERROR

New Contributor

Hi all, 

 

I have a complex Excel Workbook set up that has been running for around 6 months and worked perfectly. 

It's set up so that I copy and paste data from external reports into various tabs ("Data From A" "Data From B" etc). I have a "Calculations" Tab that identifies values within these "Data from..." tabs. Then data from my "Calculations" tab are then referenced in my "Templates" Tab 

 

This has all been working fine (and had been for 6 months), until recently when it suddenly stopped. 

 

I three tabs of interest involved in my problem: 

1: Data From CCR

Cell1 = AK3 (Value = 00:48:54)

Cell 2 = AJ3 (Value = 00:10:12)

Cell 3 = U33 (Value = 4)

 

2: Calculations

Cell 1 = D17 (Contents"=DatafromCCR!AJ3") Value = 00:10:12

Cell 2 = E17 (Contents "=DatafromCCR!AK3") Value = 00:48:54

Cell 3 = F17 (Contents "=SUM(C17:E17)") Value = 00:00:00  (Value SHOULD BE 00:59:06)

Cell 4 = G17 (Contents ='DataFromCCR2'!U33") Value = 4

Cell 5 = H17 Contents "=F17/G17")  Value = 00:31:26 (Value SHOULD BE 00:14:46)

 

This pattern is repeated referencing different cells from within the "Data From CCR" Tab, and the calculations remain fully functioning. 

The Format is identical for all cells (where formulas are working and non-working)

I cannot figure out what possible difference is causing the formula to not display the correct values in this one specific instance.

1 Reply

@Zen_Becki 

Cell 1 = D17 (Contents"=DatafromCCR!AJ3") Value = 00:10:12

Cell 2 = E17 (Contents "=DatafromCCR!AK3") Value = 00:48:54

 

Is it possible that those values are formatted as text rather than numeric? That could explain the value of zero in the next line:

Cell 3 = F17 (Contents "=SUM(C17:E17)") Value = 00:00:00  (Value SHOULD BE 00:59:06)

 

That aside, is it possible for you to post the actual spreadsheet, if not here then on SharePoint or Google Drive?