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