# Solved How do I return zeros using nested IF functions in Excel

November 29, 2015 at 00:38:08
Specs: Windows 7, Core i7 920/6Gb 1333MHz
 Hi, I have been trying to remove zeros from a timesheet I have compiled in excel 2010 without using the predefined option to do this under Options. I therefore want to use a formula to do this which for one or two for the columns in the SS I have been successful. However, for the remaining columns I get the response "too many arguments".I have configured the SS to show regular hours or less and then any OT worked in a separate column, but I still get the zeros problem for any preceding empty values. Here is an example of the function I have tried to use where \$I\$8 is for a cell showing 8 hrs only, d10-c10 is for 17.00-08.00 for example and f10 is the result (I hoped). This works for a numerical value but also returns zeros in the empty cells of the F column.Any help with this formula would be much appreciated.Thanks=IF(\$E10>\$I\$8,\$I\$8,IF(\$D10-\$C10<8,\$D10-\$C10,IF(\$F10<1,"")))message edited by batar

See More: How do I return zeros using nested IF functions in Excel

#1
November 29, 2015 at 06:34:56
 Something like this might work:=IF(\$E10<\$I\$8,"",IF(\$E10>\$I\$8,\$I\$8,IF(\$D10-\$C10<8,\$D10-\$C10,IF(F10<1,""))))It appears to be the I8 cell that is returning the zero, but without seeing what your sheets looks like, it is difficult.If you could post a small sample of how your sheet lookslike this:``` C D E F G I 1) Heading Heading Heading Heading Heading Heading 7) 8) ?? 9) 10) ?? ?? ?? ```So we get a better understanding of what your doing, itwould help.Read this HOW-TO which explains the use of the < PRE > tagsto align your data like the above.http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

#2
November 29, 2015 at 08:10:58
 Hi Mike,I have included column letters and row numbers and also a greater idea of how the sheet looks. As you can see it is returning zeros and "value" which I haven't any idea about. I originally followed a video tutorial for calculating overtime against regulars hours in which the guy suggested for column F to use the 8.00 hr "regular" hours (or less) using a single cell with 8.00 inputted (as previously mentioned, the \$I\$8 cell). Hence, the F column looking like it does. I have altered the code to remove zeros etc. Hope this helps.Hrs = [=IF(\$D18-\$C18,\$D18-\$C18,"")]Reg = [=IF(\$E10>\$I\$8,\$I\$8,IF(\$D10-\$C10<8,\$D10-\$C10,IF(\$F10<1,"")))]Extra Hrs = [=IF(\$E10>\$F10,\$E10-\$F10,"")]```B C D E F G 10 0:00 11 0:00 12 0:00 13 0:00 14 30/10/2015 0:00 15 31/10/2015 0:00 16 01/11/2015 0:00 17 0:00 18 02/11/2015 08:50:00 17:05:00 8:15 8:00 0:15 19 03/11/2015 08:45:00 17:00:00 8:15 8:00 0:15 20 04/11/2015 09:10:00 17:00:00 7:50 7:50 21 05/11/2015 09:30:00 17:00:00 7:30 7:30 22 06/11/2015 09:00:00 17:00:00 8:00 8:00 23 07/11/2015 8:00 #VALUE! 24 08/11/2015 8:00 #VALUE! 8:00 #VALUE! ```Richardmessage edited by batar

Report •

#3
November 29, 2015 at 08:54:56
 You did not put in Column Letter or Row Numbers, so not sure what goes where.......MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
November 29, 2015 at 09:16:22
 Just looking over your formulas and I have a few questions.On this formula: =IF(\$D18-\$C18,\$D18-\$C18,"")The structure of an IF function is: =IF(ConditionToTest , ActionIfTrue , ActionIfFalse)Since your Condition to Test is a math equation, it will ALWAYS return TRUEbecause D18-C18 is always True, it can never be false, so it will always perform the ActionIfTrue or D18-C18Was your intent to make sure the Ending Time was greater than the Start Time?MIKEhttp://www.skeptic.com/

Report •

#5
November 29, 2015 at 09:22:58
 Take a look at this page, it is very similar to what your doing and is an excellent tutorial on working with timesheets.http://www.cpearson.com/Excel/overt...MIKEhttp://www.skeptic.com/

Report •

#6
November 29, 2015 at 09:26:54
 Note on the Pearson page the 8 Hours is placed, once, on Row 2 along with the Overtime Rate. Also , note at the bottom you can download the example sheet.MIKEmessage edited by mmcconaghy

Report •

#7
November 29, 2015 at 09:27:38
 It was simply so I could return the value of the equation if TRUE and to return a value of "nothing" if FALSE, hence "". I'm not sure what your last question is asking. If you could clarify this I would be most grateful.N.B. Column C=start time, D=finish time, E=hrs, F=reg hrs, G=OTThanks

Report •

#8
November 29, 2015 at 09:30:31
 'm not sure what your last question is asking.In many time sheets, it is possible to work beyond 24 Hours, IE Start Time of 8 PM with a End Time of 4 AM the following day.MIKEhttp://www.skeptic.com/

Report •

#9
November 29, 2015 at 09:34:06
 Great, just clarifying. Yes this was the intention as shifts will not extend much beyond the 8 hour working day.

Report •

#10
November 29, 2015 at 09:56:13
 After you have studied the Pearson sheet, if have any further question please ask.MIKEhttp://www.skeptic.com/

Report •