Extracted Data not Calculating

[NickFazer]

Hi

Please find attached file, created in Excel 2016.

I have a sheet called Datasheet to track attendance and holidays and other reasons for absence. One category I need to track is for planned maintenance, on the spreadsheet there is a dropdown list formatted General all the planned maintenance criteria are in the dropdown list as F1 to F12, the F indicating planned maintenance and the number the number of hours planned. 

I have extracted the number into a different sheet using the MID function but when I add a total column it doesn't calculate, if I manually type a number in it works so there is obviously an issue somewhere with formatting but I can't see where. The sheet where the data is extracted to is formatted number, I'm sure it is something simple but I don't know where else to change it.

Thanks

[#2 · Accepted solution]

@NickFazer  wrote: ``Please find attached file``

Missing!  Click "browse" below to attach the file.

@NickFazer  wrote: ``I have extracted the number into a different sheet using the MID function but when I add a total column it doesn't calculate``

I suspect the extracted "number" is text.  That is the type of data returned by MID.  You need to convert the "numeric" text to an actual number using an arithmetic expression.  Some examples:

--MID(...)

1*MID(...)

0+MID(...)

[#3]

Joe User

Thank you for your response, makes sense that the value is formatted as text and so won't calculate, I can't see a "Browser" button available to attach the file, why isn't that available? it should be on the toolbar. Anyway I have implemented your solution and worked, can now finish the job a wiser man.

Many Thanks

[#4]

@NickFazer  wrote: ``makes sense that the value is formatted as text and so won't calculate``

Nitpick.... The result of MID __is__ text; it is not "formatted as text".  In fact, the format of the cell does not matter.

And the text result __would__ "calculate" in __some__ contexts, if Excel can interpret the text as numeric input (which includes dates, times, percentages etc).  That is why the work around is --MID..., 1*MID... or 0+MID..., all of which are arithmetic expressions.

But you wrote that it does not "calculate" ``when I add a total column``.  Most people use a SUM-like function for that purpose.  And __those__ functions typically ignore cell values that are text.

-----

@NickFazer  wrote: ``I can't see a "Browser" button available to attach the file``

The "browser" link is at the bottom of the Reply frame, circled in red below.

[#5]

Thank you for that explanation very useful to know.