How to Simplify Nested If Function…

**Rajeshkumar R** · 03-06-2012, 08:06 AM

Hi Friends,

I spend more than three hours and formed a Nested IF Formula for one of my regular activity and its working fine. But formula is so long, as shown in the attached file “Column M” position. Kindly let me know, How to Simplfy this formula?

Thanks & Regards,
Rajeshkumar R

**jraj1106** · 03-06-2012, 08:09 AM

Can u explain what exactly do u want to achieve?

**shota** · 03-06-2012, 08:52 AM

you could write this in O2 :
=CHOOSE(IF(B2<0,1,IF(C2<0,2,IF(D2<0,3,IF(E2<0,4,IF(F2<0,5,IF(G2<0,6,7)))))),$B$1,$C$1,$D$1,$E$1,$F$1,$G$1,"Okay")

but unfortunately it's even longer

)

**darknation144** · 03-06-2012, 09:23 AM

Can you not use And() or Or() to simplify it?

**Marcol** · 03-06-2012, 11:22 AM

If all the negative cells are continuous, as your sample suggests then, in O2

Please Login or Register  to view this content.

COLUMNS($B$1:$G$1) could be replaced with 6 i.e. the number of columns in your range.

**tigeravatar** · 03-06-2012, 11:44 AM

Rajeshkumar R,

Here's my version, in cell O2 and copied down:
=IF(COUNTIF(B2:G2,"<0"),INDEX($B$1:$G$1,MATCH(TRUE,INDEX(B2:G2<0,),0)),"Okay")

**Rajeshkumar R** · 03-06-2012, 11:51 PM

Hi Friends,

Thanks a lot to all of you for quick replay, But... as I mention above THREAD I want to reduce the formula length which is there in the M2 Cell not O2.

Thanks & Regards,
Rajeshkumar R[/QUOTE]

**Marcol** · 03-07-2012, 05:09 AM

So what is to long about the formula you have in the M2 Cell?

Please Login or Register  to view this content.

If you want, as I suspect, the various returns you have in Column M, then you must tell us the conditions that apply.
I can't see any obvious logic behind the expected results.

What am I missing?