Set Cell Value by Result

  • Thread starter Thread starter ddionne
  • Start date Start date
D

ddionne

I am trying to determine what the best way would be to determine th
value of two cells in a single record using excel (based upon tabl
below) and could set the value of a third multiplyer cell.

Eg. If cell value is <=0.55 AND Units = 7 then set cell value to 0.045
IF >0.60 & <.65 AND Units = 3 Then set value = 0.150.

These are the Table values:

| Percent | Percent | Percent | Percent
Units | 0.550 | 0.600 | 0.650 | 0.700
1 | 0.055 | 0.160 | 0.260 | 0.370
2 | 0.050 | 0.160 | 0.240 | 0.330
3 | 0.050 | 0.150 | 0.210 | 0.280
4 | 0.050 | 0.150 | 0.190 | 0.240
5 | 0.050 | 0.140 | 0.160 | 0.190
6 | 0.050 | 0.120 | 0.140 | 0.160
7 | 0.045 | 0.100 | 0.120 | 0.140
8 | 0.040 | 0.090 | 0.105 | 0.120
9 | 0.035 | 0.090 | 0.090 | 0.105
10 | 0.030 | 0.075 | 0.080 | 0.095
11 | 0.030 | 0.070 | 0.075 | 0.085
12 | 0.028 | 0.060 | 0.070 | 0.080
13 | 0.025 | 0.055 | 0.065 | 0.075
14 | 0.025 | 0.050 | 0.060 | 0.070

Thanks for help
 
I am trying to determine what the best way would be to determine the
value of two cells in a single record using excel (based upon table
below) and could set the value of a third multiplyer cell.

Eg. If cell value is <=0.55 AND Units = 7 then set cell value to 0.045,
IF >0.60 & <.65 AND Units = 3 Then set value = 0.150.

These are the Table values:

| Percent | Percent | Percent | Percent
Units | 0.550 | 0.600 | 0.650 | 0.700
1 | 0.055 | 0.160 | 0.260 | 0.370
2 | 0.050 | 0.160 | 0.240 | 0.330
3 | 0.050 | 0.150 | 0.210 | 0.280
4 | 0.050 | 0.150 | 0.190 | 0.240
5 | 0.050 | 0.140 | 0.160 | 0.190
6 | 0.050 | 0.120 | 0.140 | 0.160
7 | 0.045 | 0.100 | 0.120 | 0.140
8 | 0.040 | 0.090 | 0.105 | 0.120
9 | 0.035 | 0.090 | 0.090 | 0.105
10 | 0.030 | 0.075 | 0.080 | 0.095
11 | 0.030 | 0.070 | 0.075 | 0.085
12 | 0.028 | 0.060 | 0.070 | 0.080
13 | 0.025 | 0.055 | 0.065 | 0.075
14 | 0.025 | 0.050 | 0.060 | 0.070

Thanks for help.
Click to expand...

One way

=INDEX(MyTable,MATCH(Units,INDEX(MyTable,0,1),0),IF(Cell_Value<0.55,2,
MATCH(Cell_Value,INDEX(MyTable,2,0))))

Where your table is called MyTable and Units is a cell where you put the
unit and Cell_Value a cell where you put the value you want to check for
Without a defined name it might look like

=INDEX($A$1:$E$16,MATCH(H1,$A$1:$A$16,0),IF($G$1<0.55,2,MATCH($G$1,$A$2:$E$2)))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top