agwalsh
asked on
Abs formula not working - formatting appearing like a date instead of a number
I have a workbook (not attached here ) in which I have 10 linking sheets using a formula nearly identical to the one in the attached file. My problem is with the answer I am getting is an answer that looks like a date in number format. I have checked the numbers in the original sheet and they are all numbers, not dates...but the answers are appearing like a date..
ABS-formula-not-working.xlsx
ABS-formula-not-working.xlsx
als315
I don't see any problems in uploaded workbook. May be you can prepare sample from your real workbook, where we will be able to see problem?
In that file I am not seeing the issue.
The three sheets have values:
7
15
26
which add up to 48 on the Total sheet. This is showing as 48.00 with the formatting currently applied.
Much as I don't like screenshots and prefer real files, maybe a screenshot of the sheet where you have the issue would show what you are seeing. Include all the toolbars particularly the Home tab so we can see the format in the screenshot.
Also, if you put the cursor in the cell with the issue we will be able to see the real formula.
The three sheets have values:
7
15
26
which add up to 48 on the Total sheet. This is showing as 48.00 with the formatting currently applied.
Much as I don't like screenshots and prefer real files, maybe a screenshot of the sheet where you have the issue would show what you are seeing. Include all the toolbars particularly the Home tab so we can see the format in the screenshot.
Also, if you put the cursor in the cell with the issue we will be able to see the real formula.
ASKER
Yes, what is annoying is that the example I have sent to EE WORKS fine. GRRR. Am attaching a screenshot with the culprit cells highlighted. The formula for this cell is =SUM('Sample Ward 01:Sample Ward 10'!L4) - not exactly earth-shattering. The format is number (as it is for L4 in all those sheets) . Yes I have tried re-creating it...same format appears.
ABS_screenshot.docx
ABS_screenshot.docx
What are the 10 values on the separate sheets? It would appear that one of them is somehow creating a date.
43145.5 = 12:00 on 14 Feb 2018
43145.5 = 12:00 on 14 Feb 2018
Please put the mouse cursor on the affected cell and send us the screen shoot, I want to audit the results
Regards
Chris
Regards
Chris
ASKER
@Handwembo Christopher, When I put the cursor on the cell, it gives the formula =SUM('Sample Ward 01:Sample Ward 10'!L4) in the formula bar. The number format is Number on the Home ribbon.
@Rob Henson - I've gone through all the cells and they all read Number...any suggestions on how I can test for date entries or is it just as easy to re-create the formula?
@Rob Henson - I've gone through all the cells and they all read Number...any suggestions on how I can test for date entries or is it just as easy to re-create the formula?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.