• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF/And formula

B

Blair

New Member
Is it possible to combine the following three formulas into one formula so the results can be shown in one column instead of three?

=IF(AND(AD2>AI2,K2>AD2),”Above”,””)

=IF(AND(AD2>AI2,K2<=AD2,K2>=AI2),”Within”,””)

=IF(AND(AD2<AI2,K2<AI2),”Below”,””)


Column M contains either “Buy”, “Sell”, or “Hold” which I have already calculated.


I would then like to have another formula that would, in one column, match “Buy” and “Above” and give a result of “Strong Buy”; “Buy” and “Within” and give a result of “Medium Buy” and “Buy” and “Below” and give a result of “Weak Buy”.


If there is a better way than I have outlined above to achieve the desired results I would be happy to use it.


Thanks

Blair
 
Hi, Blair!

If the conditions of each IF are mutually exclusive it's possible:

=IF(AD2>AI2,IF(K2<AI2,"Below",IF(K2<=AD2,"Within","Above")),"")

Regards!
 
Hi, Blair!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
This is a continuation of my nested IF/AND project.

Column A contains one of the following: Buy, Sell, Hold

Column B contains one of the following: Above, Within, Below

I want to match these two columns and get the following results in Column C:

[pre]
Code: 
Buy & Above = Strong Buy
Buy & Within = Medium Buy
Buy & Below = Weak Buy
Sell & Below = Strong Sell
Sell & Within = Medium Sell
Sell & Above = Weak Sell
[/pre]
Hold = Hold

Will you please give me a formula that will accomplish this goal?


Thanks

Blair
 
Hi, Blair!

=IF(A2="Hold","Hold",IF(B2="Above","Strong",IF(B2="Within","Medium","Weak"))&" "&A2)

BTW, please do me a favor, kill the guy who didn't use directly strong, medium and weak.

Regards!


EDITED: & missing before last A2
 
@Blair,


You can use this one as well, its size can be reduced if you use Ranges in LOOKUP():


Code: 
=IF(A1="Hide","Hide",A1&" "&LOOKUP(G1,{"Above","Within","Weak"},{"Strong","medium","Weak"}))


@SirJb7, Hi! :)

Regards,

Faseeh
 
@Faseeh

Hi!

Still getting paid by character? Didn't you find a looonger formula?

Regards!

PS: BTW, are you playing hide and seek?
 
Hi SirJB7

I took the last formula you sent and wound up with the formula below. The “Hold” and “Buy” part works fine but all the “Sell” signals return “False”. What did I do wrong? Can’t kill the "Strong Buy" guy. That’s me.


=IF(M147="Hold","Hold",IF(M147="BUY",IF(N147="Above","Strong Buy",IF(N147="Within","Medium Buy",IF(N147="Below","Weak Buy",IF(M147="SELL",IF(N147="Above","Weak Sell",IF(N147="Within","Medium Sell",IF(N147="Below","Strong Sell")))))))))


Blair
 
Hi, Blair!

My mistake, an "&" (unquoted) missing in the formula. Updated yet.

Your mistake, don't commit suicide, we can easily handle it for you.

Regards!

PS: I didn't adjust your posted formula.
 
Hi SirJB7

This is what I have now but I’m still getting “False” when M147=”Sell” - and Buy and Below gives a Weak BuyBUY. It seems to be picking up from column M. See excerpt from my worksheet below.


This is the formula as you corrected it.

=IF(M147="Hold","Hold",IF(M147="BUY",IF(N147="Above","Strong Buy",IF(N147="Within","Medium Buy",IF(N147="Below","Weak Buy"))&" "&M147)))


M N Signal

SELL Below FALSE

SELL Below FALSE

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Below Weak BuyBUY

BUY Above Strong Buy

BUY Above Strong Buy
 
Hi, Blair!

Your correct formula should be:

=IF(M147="Hold","Hold",IF(N147="Above","Strong",IF(N147="Within","Medium","Weak"))&" "&M147)

Regards!

PS: Adjust upper/lower case as required.


@SirJB7

Hi, myself!

So long...

He still doesn't realize, but each time he writes his life risk is increased...

Regards!
 
You did it this time. Thanks very much. I may need more help on this project - but not today. Give yourself the rest of the day "off".

Blair
 
Hi, Blair!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: I'll take your advice, but next time you'll get my bill a priori.
 
This is the next step in my worksheet construction.

Column A is the result of a formula combining numbers from several previous columns. Each day a new number will be added to the bottom of Column A. I would like to offset Column A to column C and move the entire column down or up X rows. X is a variable found in cell B1. Occasionally cell B1 will change causing Column C to begin and end a little earlier or a little later.

It sounds simple but I am unable to make it work. Can you please help?
 
Hi Blair ,


I am not sure that SirJB7 will be able to answer you over the weekend ; I have not understood your requirement , and would like you to clarify :


B1 contains a number , let us say 7.


Suppose your column A range , which we can call Col_A , is the range A3:A377.


You want to get a range which will be offset from column A by 2 columns , to column C , and which will be offset by 7 rows from row 3 ; is this correct ? If so , the range you want to get is C10:C384.


The formula to get C10:C384 from A3:A377 , using the value in cell B1 , is :


=OFFSET(Col_A,B1,2)


Narayan
 
Or,

are you wanting to move the cell contents from col A to Col C, shifted down (or up?) based on the value in cell B1?


-Sajan.
 
@NARAYANK991,Sajan

Hi!

Thanks for taking the baton, just passing by until Monday.

Regards!


Hi, Blair!

I leave you in good hands, but take care of formulas from these two guys... you'd have to face them having slept well and being very attentive.

Regards!
 
Sajan seems to have made sense of my problem. I do want to move the contents of Col A to Col C and shift the entire column up or down based on the contents of B1. It will never have to shift more that 64 rows either way (and usually only about 25) and there are plenty of blank cells above and below to accommodate the shift. Remember that I will be adding one new cell (a daily data point) at the bottom of col A each day so the formula must allow for new daily info.


I glad to hear that you give SirJB7 the weekends off. He worked hard and patiently with me last week to bring me to this point. A complete worksheet is not far off. I can almost see it from here.


Cheers

Blair
 
Hi, Blair!


As I don't work too much during the week I'm not going to reverse that behavior on the weekend, no way.


Now reading your post, just a couple of things:


a) "Column A is the result of a formula combining numbers from several previous columns."

You're a magician, the best of all. I'd pay for being able to put my hands on a worksheet that has previous columns to column A... :)


b) Now a bit less seriously, when you use NARANYANK991's formula don't use the whole column A as it wouldn't work.

This yes,

=OFFSET(Axx,B1,2)

this no,

=OFFSET(A:A,B1,2)


Regards!
 
Hi SirJB7

Hope your weekend was pleasant and fruitful. I'm still having trouble.

Cells AE61 through AE347 is the range that I wish to copy, move and paste. AG4 is the cell containing the number of cells that I wish to offset (move down) which is currently 26. AG87:AG373 is where I would like this information to come to rest. Keep in mind that the beginning will always be AE61, the offset will always be located in AG4 and the AG373 will increase by one row each day as I post the new daily data point. I’ve tried several variations on the formulas that you and narayank991 have suggested and the best I get is a zero in each cell of my Col AG. Your formula is: =OFFSET(Axx,B1,2). I have tried placing my formula in AG87 and copying down: =OFFSET($AE$61,$AG$4,2). This would permanently fix the beginning of my range to be moved and the cell where my variable resides and indicate the cell where the results should begin. What am I doing wrong?

Cheers

Blair
 
Hi Blair ,


I will let SirJB7 respond to whatever your questions are , but I would like to mention that when an answer is given , I think it is expected that the person who posted the question will take the trouble to read and understand the answer , and ask further questions if required.


I had specifically mentioned , and I'll quote from my earlier post :



Suppose your column A range , which we can call Col_A , is the range A3:A377.


You want to get a range which will be offset from column A by 2 columns , to column C , and which will be offset by 7 rows from row 3 ; is this correct ? If so , the range you want to get is C10:C384.


The formula to get C10:C384 from A3:A377 , using the value in cell B1 , is :


=OFFSET(Col_A,B1,2)




What this means is that the first parameter to the OFFSET function is a range , which I have named Col_A , and specified as A3:A377.


You have altered this to :


=OFFSET($AE$61,$AG$4,2)


where the first parameter is now a single cell. I do not know why.


Narayan
 
Please forgive me. My previous post was an attempt to state my problem in the simplest of terms. Today's post states my problem (I hope) in a way the will be clearer and less subject to misunderstanding. Can we still be friends?
 
Hi Blair ,


Sorry if I sounded offensive ; my point was that a lot of time and effort is spent in probably wasted communication ; let us go back to what you want to do ; let me state it in my own words , you can correct me if I am wrong.


1. You wish to move data in the range AE61:AE347 to another place in column AG.


2. The starting point in column AG is not AG61 automatically ; it will be decided by what is present in cell AG4 ; thus if AG4 contains 0 , then AE61:AE347 goes to AG61:AG347. If AG4 contains 1 , then AE61:AE347 goes to AG62:AG348 , and so on.


3. You mention copy and paste ; copy and paste is an action ; this cannot be achieved using formulae ; you need a macro for this. If you just want that the data in column AG should reflect the data in column AE using the rules given in 1 and 2 above , then even formulae can do the job.


4. Either way , if you need to specify a destination , which is offset by a certain number of rows from a source , then the destination will be : OFFSET(source_range_address,rows_offset,columns_offset) ; in your case , since the cell AG4 contains the row offset , the second parameter will be $AG$4. Since column AG is 2 columns away from column AE , the third parameter will be 2. The first parameter will have to be a range ( and not a single cell ) , which in your case will be $AE$61:$AE$347.


You need to clarify whether you want formulae or a macro.


Narayan


P.S. We are all friends on this forum !
 
Hey Narayan

I’m back from lunch and ready to go to work. Perhaps the OFFSET formula is not what I need. You tell me. Your understanding of point 1 is correct. I simply want to duplicate the data in Col AE to Col AG. Point 2 – AE61 will always be the starting place. There is nothing above it in column AE. The contents of AG4 indicate how far down Col AG to move what it found in AE61. The contents of Col AG will be identical to Col AE except they will start x rows (the contents of AG4) down from AG61. Point 3 – Copy and paste is the effect I want to achieve but not necessarily the method of achieving it. I’m not sure if my formula should be in AG61 or AG87. My new formula is =OFFSET($AE$61:$AE347,$AG$4,2). If I put it into AG61 it returns #VALUE!. If I put it into AG87 it returns zero. There must be something else that I don’t understand.

Cheers

Blair
 
You must log in or register to reply here.
Back
Top