Tricky Problem

**pytelium** · 08-12-2005, 05:07 AM

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 · 08-12-2005, 08:05 AM

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.

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

"pytelium" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile:
> http://www.excelforum.com/member.php...o&userid=25521
> View this thread: http://www.excelforum.com/showthread...hreadid=395262
>

**pytelium** · 08-12-2005, 08:27 AM

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

Alan Beban · 08-12-2005, 12:05 PM

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 wrote:
> 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.
>
>

**pytelium** · 08-12-2005, 04:21 PM

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

Arvi Laanemets · 08-13-2005, 03:05 AM

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)
>> If the values in col c are greater than the correct values I want no
>> action taken,in col d.
'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" <[email protected]> wrote in
message news:[email protected]...
>
> I just want to write "yes" into d1 if both conditions are
> fulfilled,otherwise "no"
>
>
> --
> pytelium
> ------------------------------------------------------------------------
> pytelium's Profile:
http://www.excelforum.com/member.php...o&userid=25521
> View this thread: http://www.excelforum.com/showthread...hreadid=395262
>