mardi 19 février 2019

complex IF statements with multiple variables possibilities

I need help with making some IF/OR/AND statements.

I have a cell (C8) that can be one of fourteen different variables. Depending on the value for C8 either cells F8, D8, or E8 will be used in three possible equations.

        C   D   E   F   G   H
7                           
8                           
9   

C8 can equal any of the following values

0.5,0.55,0.6,0.7,0.75,1,1.0625,1.125,1.1875,1.25,1.325,1.375,1.4375,1.5     

Equations needed:

  • IF C8 equals any values from 0.6 - 1.5 will then need to solve for (100-(F8-108)*5))+(G8+1))

  • IF C8 equals 0.5 will then need to solve for (100-((D8-56)*5)+(G8*1))

  • IF C8 equals 0.55 will then need to solve for (100-((E8-102)*5)+(G8*1)

I currently have this equation C8 if values are 0.6 1.5

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH({0.6,0.65,0.7,0.75,1,1.0625,1.125,1.1875,1.25,1.325,1.375,1.4375,1.5},C8)))>0),100-(((F8-108)*5)+(G8*1)),"")

I think I need an IF/OR statement for two additional Situation

  • C8 equals 0.5 to solve for 100-(((D8-56)*5)+(G8*1))
  • C8 equals 0.55 to solve for 100-(((E8-102)*5)+(G8*1))

The following is the they type of IF/OR formulas I have tried.

=IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({0.6,0.65,0.7,0.75,1,1.0625,1.125,1.1875,1.25,1.325,1.375,1.4375,1.5},C8)))>0), 100-(((F8-108)*5)+(G8*1)), OR(ISNUMBER(SEARCH({0.5,C8)))>0)100-(((D8-56)*5)+(G8*1)), OR(ISNUMBER(SEARCH({0.55,C8))>0)100-(((E8-102*5)+(G8*1))"")

=IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({0.6,0.65,0.7,0.75,1,1.0625,1.125,1.1875,1.25,1.325,1.375,1.4375,1.5},C8)))>0), 100-(((F8-108)*5)+(G8*1)), (ISNUMBER(SEARCH({0.5,C8)))>0)100-(((D8-56)*5)+(G8*1)), (ISNUMBER(SEARCH({0.55,C8))>0)100-(((E8-102*5)+(G8*1))"")

Aucun commentaire:

Enregistrer un commentaire