Joining some conditions wutzke <michael.wutzke[ at ]gmail.com> 12/29/2008 7:15:34 PM I have several IF statements that I'd like to join into just one. (I use row 10 as an example) =IF(LEFT(B10,1)="A",I10-0.01,J10) =IF(OR(C10="BOG",C10="BLM"),I10-0.01,J10) =IF(LEFT(B10,1)="W",I10-0.01,J10) =IF(FIND("SHORT",G10)>0,I10-0.01,J10) =IF(LEFT(B10,1)="F",I10-0.01,J10) =IF(LEFT(B10,1)="U",I10-0.01,J10) =IF(LEFT(B10,1)="Y",I10-0.01,J10) Basically checking the values of column B, C, or G and depending on the success of the match returning the value of column I (minus .01). If nothing matches, I return the value in column J. I need to do this for every row, in a sheet that contains over 13,000 rows My IF and OR get very confusing. Any suggestions?

Re: Joining some conditions JE McGimpsey <jemcgimpsey[ at ]mvps.org> 12/29/2008 7:44:20 PM One way: =IF(OR(AND(B10<>"", ISNUMBER(FIND(LEFT(B10,1), "AWFUY"))), ISNUMBER(FIND("SHORT", G10)), SUM(COUNTIF(C10, {"BOG","BLM"}))), I10-0.01, J10) In article <c2fc66f9-438c-4d40-8e65-3ccaedd32e37[ at ]c36g2000prc.googlegroups.com>, wutzke <michael.wutzke[ at ]gmail.com> wrote: [Quoted Text] > I have several IF statements that I'd like to join into just one. > (I use row 10 as an example) > > =IF(LEFT(B10,1)="A",I10-0.01,J10) > =IF(OR(C10="BOG",C10="BLM"),I10-0.01,J10) > =IF(LEFT(B10,1)="W",I10-0.01,J10) > =IF(FIND("SHORT",G10)>0,I10-0.01,J10) > =IF(LEFT(B10,1)="F",I10-0.01,J10) > =IF(LEFT(B10,1)="U",I10-0.01,J10) > =IF(LEFT(B10,1)="Y",I10-0.01,J10) > > Basically checking the values of column B, C, or G and depending on > the success of the match returning the value of column I (minus .01). > If nothing matches, I return the value in column J. > > I need to do this for every row, in a sheet that contains over 13,000 > rows > > > My IF and OR get very confusing. Any suggestions?

Re: Joining some conditions wutzke <michael.wutzke[ at ]gmail.com> 12/30/2008 1:09:09 PM On Dec 29, 11:44 am, JE McGimpsey <jemcgimp...[ at ]mvps.org> wrote: [Quoted Text] > One way: > >    =IF(OR(AND(B10<>"", ISNUMBER(FIND(LEFT(B10,1), "AWFUY"))), > ISNUMBER(FIND("SHORT", G10)), SUM(COUNTIF(C10, {"BOG","BLM"}))), > I10-0.01, J10) > > In article > <c2fc66f9-438c-4d40-8e65-3ccaedd32...[ at ]c36g2000prc.googlegroups.com>, > >  wutzke <michael.wut...[ at ]gmail.com> wrote: > > I have several IF statements that I'd like to join into just one. > > (I use row 10 as an example) > > > =IF(LEFT(B10,1)="A",I10-0.01,J10) > > =IF(OR(C10="BOG",C10="BLM"),I10-0.01,J10) > > =IF(LEFT(B10,1)="W",I10-0.01,J10) > > =IF(FIND("SHORT",G10)>0,I10-0.01,J10) > > =IF(LEFT(B10,1)="F",I10-0.01,J10) > > =IF(LEFT(B10,1)="U",I10-0.01,J10) > > =IF(LEFT(B10,1)="Y",I10-0.01,J10) > > > Basically checking the values of column B, C, or G and depending on > > the success of the match returning the value of column I (minus .01). > > If nothing matches, I return the value in column J. > > > I need to do this for every row, in a sheet that contains over 13,000 > > rows > > > My IF and OR get very confusing. Any suggestions? Thanks thanks works great. great example of using functions to their fullest