reporting values using IF function; nested arrays

**rippa** · 02-14-2011, 01:45 AM

Hey everybody,
Can smbd help me to figure out this formula:
I need excel to report any value <$C$2 from column C as "YES".

So far I've tried this formula:

IF (C$4:C$8<$C$2,"YES","") except this formular works only for the frist line.

I also need to use nested arrays as I want to be able to sort this column later without data loss.

**arthurbr** · 02-14-2011, 03:00 AM

Try =IF (C4<$C$2,"YES","") and pull down as necessary

**Jamesr571** · 02-14-2011, 05:42 AM

Originally Posted by rippa

Hey everybody,
Can smbd help me to figure out this formula:
I need excel to report any value <$C$2 from column C as "YES".

So far I've tried this formula:

IF (C$4:C$8<$C$2,"YES","") except this formular works only for the frist line.

I also need to use nested arrays as I want to be able to sort this column later without data loss.

The reason this doesnt work is because you are typing it into the one cell. If you drag Arthurbr it will not work either due to the constants.

The formula you need is as follows;

IF(C4<$C$2","YES","")

Then if you drag this formula down it will change the cell to check but not the cell you wish to check it against.

Hope this Helps.

**DonkeyOte** · 02-14-2011, 06:10 AM

Originally Posted by Jamesr571

If you drag Arthurbr it will not work either due to the constants.

@Jamesr571, your formula would appear to be identical to that suggested by arthurbr's - are we missing something ?

edit:

FWIW, the OP's formula

Please Login or Register  to view this content.

should work without issue if applied to D4:D8 - pasted anywhere other than D4:D8 it would not.

In D4:D8 XL will use C4:C8 as appropriate (ie D4 uses C4, D5 uses C5 etc...)

**rippa** · 02-15-2011, 11:38 PM

Wow guys, thanks a lot!
as usually, I complicated everything too much

**Jamesr571** · 02-16-2011, 05:27 AM

Yes there is a difference.

instead of C$4$ use C4 that way when you drag down it will use column C.

using C$4$ would mean when you drag down it will always use only C4 to compare it too. Using C4 it will then become C5, C6, etc, as you drag it down so there is a difference.

**DonkeyOte** · 02-16-2011, 05:46 AM

Originally Posted by Jamesr571

Yes there is a difference.

Originally Posted by arthurbr

=IF (C4<$C$2,"YES","")

Originally Posted by Jamesr571

IF(C4<$C$2","YES","")

I suspect one of us needs to visit the opticians - though I agree that technically yours does differ slightly - the quotation after $C$2 would obviously cause issues.

To reiterate though, OP's formula would work if it was being applied to D4:D8 as implied

edit: attached sample to illustrate above