Thread: IF function with OR nested - only works on some cells?!

.

HALP!

I am creating a staff schedule input sheet (Excel 2010) so that I don't have to manually change each page of my shedule worksheet for every event. I'm by no means an Excel novice, but I'm SURE there is just something small and stupid that I'm missing here.

Basically, to me, it's looking like my =IF(OR... etc.) functions are chosing to only work in some cells and not others when I copy them down. All refercence cells *seem* to be in the same format, so I don't know why I'm getting different results.

The workbook is attached - the problem I'm running into is on the "Day 1" tab - the range that contains the formulas I'm having difficulty with is C15:AG39.

Any insight would be much appreciated.

Here are two pictures for quick reference (one with formulas showing and one without):

http://img.photobucket.com/albums/v4...thFormulas.jpg

http://img.photobucket.com/albums/v4...utFormulas.jpg

Thanks in advance for taking the time to help me!

Staff Break Schedule Input Sheet.xlsx

This will only work the way you have it if the times in C9 C10 EQUAL the time in column A
Since the only times listed in column C's "headers" are 2pm 5pm these are the only ones that are showing up.
I suspect you want a > < comparison not an =

Duplicated

It has do with Excel's precision limitations when you are comparing times.. since they are to infinite decimals....

try rounding each reference to 6 decimals... and because you have some "n/a" values you need to use N() function to convert text to 0...

eg.

=IF(OR(ROUND(N($E$9),6)=ROUND(A15,6),ROUND(N($E$10),6)=ROUND(A15,6),ROUND(N($E$11),6)=ROUND(A15,6)), $E$12," ")

copied down

or you could use like:

=IF(COUNT(IF(ROUND(IF($C$9:$C$11<>"n/a",$C$9:$C$11,0),6)=ROUND(A15,6),1)),$C$12,"")

confirmed with CTRL+SHIFT+ENTER