Joining some conditions

  • Thread starter Thread starter wutzke
  • Start date Start date
W

wutzke

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?
 
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)
 
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)
Click to expand...

Thanks
thanks works great.
great example of using functions to their fullest
 
Back
Top