Abs formula not working - formatting appearing like a date instead of a number

agwalsh
agwalsh used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Rob HensonFinance Analyst

Commented:
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.
Hi

There is nothing wrong just check format with your computer, see attached
Sample.docx
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Rob HensonFinance Analyst

Commented:
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
Please put the mouse cursor on the affected cell and send us the screen shoot, I want to audit the results

Regards

Chris

Author

Commented:
@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?
Commented:
What I had done was added in an extra sheet and this extra sheet had dates in the same row as my formula which knocked out my formula. I amended it and now it works fine. Yikes, always the simple things :-) Thanks for your help as always.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial