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

Report •

#1
November 29, 2015 at 06:34:56
✔ Best Answer
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 looks
like 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, it
would help.

Read this HOW-TO which explains the use of the < PRE > tags
to align your data like the above.

http://www.computing.net/howtos/sho...

MIKE

http://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.

http://www.bing.com/videos/search?q...

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!

Richard

message 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.......

MIKE

http://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 TRUE
because D18-C18 is always True,
it can never be false,
so it will always perform the ActionIfTrue or D18-C18

Was your intent to make sure the Ending Time was greater than the Start Time?

MIKE

http://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...

MIKE

http://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.

MIKE

http://www.skeptic.com/

message 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=OT

Thanks


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.

MIKE

http://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.

MIKE

http://www.skeptic.com/


Report •

Ask Question