compate 3 columns values

[ambcw]

Hello Experts,
Can anybody please help me with this please.
I’m trying to compare 2 columns to 1 column and it will give me “text comment” when condition meets.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
For example,
A B C D
2 1 3 if B < A “allocate more” or if C < A “allocate more”, if any of B or C more than A, return is blank cell
<o> </o>
Any help would much appreciated
Cheers
Andre

 

[ambcw]

Sorry I revised the example, I don't know what happen but other symbol didn;t appear

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For example,
Column A --Column B --Column C-- Column D
2 ------------ 1----------- 3 --------------if B less than A, "text" or C less than A, "text", and either B or C is more than A, cell is blank.
<o></o>

Any help would much appreciated
Cheers

 

[jeffreybrown]

Maybe...

=IF(OR(B1 < A1,C1 < A1),"text",IF(OR(B1 > A1,C1 > A1),""))

 

[ambcw]

Thanks Jeff,

I've tried the formula but It didn't satisfy the result I want.

here's the result.

<TABLE style="WIDTH: 251pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=334 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2528" width=79><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 4544" width=142><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1408" width=44><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=79 height=20>A</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 107pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=142>Text</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 52pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=69>B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 33pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=44>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8 0.5pt; BORDER-BOTTOM: #d4d0c8 0.5pt; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent">Contractor to scope</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent">1</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #c5d9f1">5</TD></TR></TBODY></TABLE>

The text should be Blank, since C more than A. what I'm trying to do is alert me if any of B or C value is less than A.

Cheers

 

[Peter_SSs]

<TABLE style="WIDTH: 251pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=334 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2528" width=79><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 4544" width=142><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2208" width=69><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1408" width=44><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=79 height=20>A</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 107pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=142>Text</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 52pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=69>B</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 33pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=44>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8 0.5pt; BORDER-BOTTOM: #d4d0c8 0.5pt; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent">Contractor to scope</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent">1</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8 0.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #c5d9f1">5</TD></TR></TBODY></TABLE>

The text should be Blank, since C more than A. what I'm trying to do is alert me if any of B or C value is less than A.

This is contradictory ..

C more than A is True (means result should be blank)
B or C value is less than A is also True because B is less than A (means result should alert you)


Do you mean "alert you if BOTH B AND C are less than A"?

Also,

1. Can either column B and/or column C be blank when column A has a value? If so, what should the result be?

2. What happens if values are EQUAL to column A? For example, what would these results be?

5, 5, 5

5, 6, 5

5, 5, 3

 

[ambcw]

Sorry if it becomes confusing.
If value of either B & C is less than A, I will allocate more jobs
If value of any of the two (B & C) is more than A, I will do nothing
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> <TABLE style="WIDTH: 255pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=339 border=0><COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816" width=88><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 2976" width=93><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 66pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=88 height=19>A</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 70pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=93>B</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 54pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=72>C</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=86>Text Column</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=right height=19>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>2</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> allocate</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=right height=19>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1 </TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">blank</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=right height=19>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> blank</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=right height=19>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>5</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1 </TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">blank</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" align=right height=19>3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>7</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> blank</TD></TR></TBODY></TABLE></o>
I hope this clear up the confusion
<o> </o>
Cheers

 

[Peter_SSs]

Sorry if it becomes confusing.
If value of either B & C is less than A, I will allocate more jobs
If value of any of the two (B & C) is more than A, I will do nothing
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>

Excel Workbook
	B	C	Text Column
3	1	2	allocate
3	3	1	blank
3	2	3	blank
3	5	1	blank
3	1	7	blank
Sheet

</o>
I hope this clear up the confusion
<o> </o>
Cheers

I think your example has clarified but your wording is still incorrect. If your examples are correct then

If value of either B & C is less than A, I will allocate more jobs

should say

If values of both B & C are less than A, I will allocate more jobs

You also didn't answer the question about whether there might be any blanks in columns B or C but see if this does what you want.

Formula in D2 is copied down.

Excel Workbook
	A	B	C	D
1
2	3	1	2	Allocate
3	3	3	1
4	3	2	3
5	3	5	1
6	3	1	7
Alert

 

[ambcw]

Thanks for the patience Peter, much appreciated.

Spot on mate, problem solved. .

Cheers