Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Anonymous
(Unregistered)Tue 31-Jan-12 20:32:35
Print Post

Some Excel Formula help required please


[link to this post]
 
Im not that great with excel formulas so could do with some help if anyone could.

Lets say in column A there could be any number between 1 and 52
In column B there will be some text, for this lets say hello, goodbye, happy ,sad.

The cell with the formula in (lets say E1) I need it to look at the data in column A & B and for this example lets say I want E1 to return true if any of that data in A = 4 and the B cell next to it contains hello.

Any help appreciated.
Standard User withnep
(newbie) Tue 31-Jan-12 21:01:13
Print Post

Re: Some Excel Formula help required please


[re: Anonymous] [link to this post]
 
Use the AND function

E1 would be =AND($A(row number)=4,$B(row number) ="hello")

Use the $ so that if you drag the formula across from E at any point it will still work.

Hope this helps.

Peter
Anonymous
(Unregistered)Tue 31-Jan-12 21:44:12
Print Post

Re: Some Excel Formula help required please


[re: withnep] [link to this post]
 
Thanks for the formula but i get an error and it highlights $A as the error.

From the look of the forumla it is just reading from a specified row in column A & B. I need the cell to look at all rows in the columns and return a true if any of the rows in column A with a 4 in and the B cell next to it has a hello. It should return a false if the conditions are not met.


Register (or login) on our website and you will not see this ad.

Standard User camieabz
(sensei) Tue 31-Jan-12 21:52:44
Print Post

Re: Some Excel Formula help required please


[re: Anonymous] [link to this post]
 
=AND(COUNTIF(A:A,52),COUNTIF(B:B,"hello"))

Edit: Will return "TRUE" if the number 52 is in column A, and the text "hello" is in column B. Will return FALSE if either or both are not present. If you want to make it work for either, change AND to OR.

~~~~~~~~~~


© Camieabz 2002-2011

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”

Edited by camieabz (Tue 31-Jan-12 21:55:18)

Anonymous
(Unregistered)Tue 31-Jan-12 22:13:26
Print Post

Re: Some Excel Formula help required please


[re: camieabz] [link to this post]
 
Spot on camieabz, worked just as i needed.

Thanks to both of you.
Standard User XRaySpeX
(eat-sleep-adslguide) Tue 31-Jan-12 22:38:38
Print Post

Re: Some Excel Formula help required please


[re: Anonymous] [link to this post]
 
In reply to a post by Anonymous:
and the B cell next to it contains hello.
That last suggestion will return TRUE even if the "hello" is not next to the 4, which is not what you want.

What you need is something like:
=VLOOKUP(4,A2:B11,2,FALSE)="hello"

You may need to get more involved if 4 never appears anywhere, to cope with the resultant "#N/A".

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Anonymous
(Unregistered)Tue 31-Jan-12 22:51:49
Print Post

Re: Some Excel Formula help required please


[re: Anonymous] [link to this post]
 
Oops, It would seem I was a bit premature with the celebration.

It works only when there is just one number in column A. As soon as you have different numbers in Column A it returns a TRUE even if I am only looking for it to return a TRUE for "hello" it will still return a true for all the other "TEXT" as well.
Anonymous
(Unregistered)Tue 31-Jan-12 22:57:34
Print Post

Re: Some Excel Formula help required please


[re: XRaySpeX] [link to this post]
 
Hi XraySpeX,

I have just tried yours and it always returns a FALSE for whatever "TEXT" there is in column B.
Standard User XRaySpeX
(eat-sleep-adslguide) Tue 31-Jan-12 23:03:26
Print Post

Re: Some Excel Formula help required please


[re: Anonymous] [link to this post]
 
Please copy and post your formula here?

Remember that this will only do an exact match on the TEXT. If you have surrounding spaces or different case it will fail unless you get more complicated again.

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User camieabz
(sensei) Tue 31-Jan-12 23:27:56
Print Post

Re: Some Excel Formula help required please


[re: Anonymous] [link to this post]
 
Ahh. Misread the requirements. Here's one way:

Pick a cell for the numeric entry. I picked D1 in this instance. In cell E1 enter:

=IF(OR(COUNTIF(A:A,"<D1"),COUNTIF(A:A,">D1")),"Error!",VLOOKUP(D1,A:B,2,FALSE))

That line looks for the instances of D1 number in column A, returns error if there's zero or more than one, but if a single instance, returns the contents of the B cell next to it.

~~~~~~~~~~


© Camieabz 2002-2011

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to