Complicated IF function with example

[ruthyeh]

I have already posted this problem but I have tried to better describe it here and attached an example of the spread sheet -
excel forum example.xlsx
I have a complicated IF function problem I'm struggling with...
I need to say-
IF = (K10=1) look at value in column C. If mean value between C10:C20 is larger than value found in C10, then look at mean value between L10:L20, if this value is larger than L10 then insert a '1' into column M, if not insert a '0'.

Thanks in advance, I'm stuck with this one!

[Pete_UK]

Try this:

=IF(K10=1,IF(C10<=AVERAGE(C10:C20),IF(L10<=AVERAGE(L10:L20),1,0)),0)

Hope this helps.

Pete

[ruthyeh]

Great, that works, thanks!

[ruthyeh]

It seems to be working but in some cells instead of returning a '1' or a '0', it is inserting a 'FALSE', why might this be?

[Pete_UK]

Well, you didn't define all the conditions, but try this instead:

=IF(K10=1,IF(C10<=AVERAGE(C10:C20),IF(L10<=AVERAGE(L10:L20),1,0),0),0)

(change shown in red).

Hope this helps.

Pete

[ruthyeh]

That's great thanks