# Error value present in cell causing formulas to be negated.

0

I created a spreadsheet, designed for payroll scheduling. I've received no formal training in excel, have learned everything I do know through trial and error and internet searches. I will list the series of formulas in generic cell pattern and describe formatting to the best of my ability. Unfortunately,  I don't have a home computer at the moment and this worksheet is on my work computer, so I don't have specific codes at the moment. Here goes...(A1,A2) input number in "military" time format. (A3,A4) convert (A1,A2) values to display actual time in hh:mm. (B1) =sum(A3-A4)converted to numeric decimal (B2) recognizes a greater than number value in(B1) as true. (B3) converts true value in (B2) to ".5" and if "false displays "0".(B4)=Sum(B3-B1). (C1)=(B4). (C2)converts (B2) value to "L". I hope that makes sense to you. The problem I have is (A1,A2) carries the potential of being empty, if (A1,A2) are blank (A3,A4) display #VALUE! Error Symbol and causes the formulas in (B1)(C1)(B4). I've tried the recommended IFERROR,ERROR_TYPE,REPLACE,REPLACEIF,CONVERT, CONVERTIF AND CONDITIONAL FORMATTING formula settings. I can get(A3,A4) to display 12:00am but still interferes with (B1,C1,B4) formula displays.I don't know if I'm using them correctly or In the right order or if the formulas I obtained are correct. PLEASE HELP!!!! I would need a layman's version of the correct formulas and steps to fix this. Thank you in advance for any and all help.

0

"Convert value to L" makes no sense to me, and:

``converts true value in (B2) to ".5" and if "false displays "0"``

indicates that you don't differentiate between numbers and strings. This should be

``=IF(B2=TRUE, 0.5, 0)``

Excel loves numbers and is bad with strings. Strings are text, marked by surrounding quotation marks. Text can't be used in multiplications (can't multiply "Yes" with "No").

Now, it seems that you have a string in A1, such as "1100" which is very different from the number 1100. It could also be that you have "" in A1 which is a string (indicated by the quotation marks) and not the same as 0 (zero).

In A3 you should have a formula like

``=A1/2400``
because that would convert your "military time" to 24-hour time (which you could display - but not convert! - to AM/PM). This would cause a #VALUE error if A1 holds a string instead of the expected number, even if the string is "" making you believe that the cell is "empty". It is not (since it contains "") and would cause an error which it would not if its value were 0. So, if you have a formula writing "" to A1, change that formula to write 0 (zero) instead. However,
``=IFERROR(A1/2400,0)``
would cure that problem.

I suggest you read up on how Excel handles time, and defnitely get rid of all the strings in your calculations. Then you should be just fine.

I wrote about this a few days ago. Take a look.

### Discussion

Thank you so much for your help, I'm very much trying to learn what I can. I will research more the area's you discussed. Is there a specific site I can go to for training that teaches and explains in a more of a "layman's" fashion. I've tried reading instruction guides before but the terminology is very confusing. Also, if you could, what would be the correct method for getting a cell to find either true or .5 value in another cell and display an L in originally selected cell? A now former colleague of mine created formula I'm currently using and I think his version might be wrong. Thanks again so much for your help, it is sincerely appreciated.
KEBinAZ (rep: 4) Sep 14, '17 at 9:40 am