Tricky Problem

[pytelium]

I have a spreadshhet with 3 columns,I wish to create a fourth column
which
carries out an action,if 2 conditions are met.

col a has value that is greater than 6

col c value is the appropriate value for the range in which values
of b fall.

col b contains numbers in the range 1 to 1000.

The categories in col b and appropriate values in col c are shown
below.

If the values in col c are greater than the correct values I want no
action taken,in col d.




col b col c

1 to 2 0.01

2.02 to 3 0.02

3.05 to 4 0.05

4.1 to 6 0.1

6.2 to 10 0.2

10.5 to 20 0.5

21 to 30 1

32 to 50 2

55 to 100 5

110 to 1000 10

an example sheet
A B C D
6.3 3.2 .05
2.58 4.6 0.4
0.24 4.2 0.3
0.43 10 13
4.14 21 11.5
0.64 17.5 51.5
0.26 17 63
0.82 24 84
1.00 27 113
0.90 23 116
0.38 38 132
1.29 26 142
0.90 26 264
0.38 26 234
0.75 32 388
0.94 32 118

here only the 1st row fulfils the 2 conditions.

 

[Arvi Laanemets]

Hi

What exactly must the function do. In Excel functions can't carry out any
actions by definition - they only can display a result. To carry out an
action (open some file, change the cursor position, activate a cell, write
some value into cell, change the cell formatting, etc., is allowed for
procedures only.

 

[pytelium]

I just want to write "yes" into d1 if both conditions ar
fulfilled,otherwise "no

 

[Alan Beban]

You stated "if 2 conditions are met"; but then you enumerated 3 conditions:

1. Column A value > 6
2. Column B is a number between 1 and 1000
3. Column c is a number not greater than a a specified value

So what's up?

Alan Beban

 

[pytelium]

sorry,column B will always be a number between 1 and 1000.

 

[Arvi Laanemets]

Hi

"Write" or "display" - there is a huge difference, and I assume you meaned
"display"

In general, you need a simple IF-formula:
=IF(LogicalCondition,"yes","no")
The logical condition must be composite - 3 conditions must be true at same
time:
=IF(AND(Condition1,Condition2,Condition3),"yes","no")

Condition1 - the value in column A must be > 6 (like 'A2>6')
Condition2 - the value in column B must be >=1 and <=1000 {like
'AND(B2>=1,B2<=1000)'}
Condition3 - ... here are some problems with it!
a)'No action' means that nothing is displayed, or "no" is displayed?
b) I see that:
when in column B is 2, and in column C is 0.01, then "yes" must be
displayed;
when in column B is 2, and in column C is 0.02, then "no" (or "") must
be displayed;
when in column B is 3, and in column C is 0.02, then "yes" must be
displayed;
but what is displayed, when in column B is 3, and in column C is 0.01? From
your explanation follows that "yes"?
c) And what about cases, when in column B are values like 2.01, or 10.4, or
105 ?
Without sorting this out before, there is no way to give a right advice.


Arvi Laanemets

 

[pytelium]

Arvi

Thanks very much for your help,I now have a solution to my problem,I am
afraid I did not explain the problem very well.

Pytelium