• 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.

IF and condition not met then go to next row ?

kerstin boelsen

New Member
Hi,

I'm quite an Excel novice.

I try to find a good way to tell Excel
"if A2 = apple,B2= banana, show me the content of F", but if A2 is not apple and/ or B2 is not banana, go to the next row and try again.
I can get Excel to show me "-" if the condition is not met, but I can't make it "jump"; I need to display the content of a mixed large data file on tab 2 in defined cells of the "display" on the main tab
=IF(AND('2nd tab'!A2="apple", '2nd tab'!B2="Banana"),'2nd tab'!F2,"-") was as far as I got :(

And what would ctrl-shift-enter do ?
 

NARAYANK991

Excel Ninja
Hi ,

I am not clear on what some of your explanations mean ; if you can upload your workbook , and then explain with reference to the data in the workbook , it might make it easier to solve your problem.

Narayan
 

kerstin boelsen

New Member
Hi ,

I am not clear on what some of your explanations mean ; if you can upload your workbook , and then explain with reference to the data in the workbook , it might make it easier to solve your problem.

Narayan

I have 2 tabs (tab 1 which is to display content of certain cells in tab 2)
in tab 1 i need to display in cell A1 (tab 1) the content of cell F2 (on tab2), but only if cell A2 is a certain value (apple) AND cell B2 is also a certain value (banana).
If not both conditions are met, the formula should continue with the next row and check if both conditions in A3 and B3 are met in the next row and then display the content of that F3. Tab 2 contains 105k lines and the content is to be linked further with other workbooks; just filtering and displaying would not work as it needs to be repeatable with only redefinition of A2 & B2.
with the formula above, I get the data I need but have a lot of "-" when the conditions are not met. instead of a large number of rows "-", I want Excel to jump to the next row and check the conditions there until a suitable row for cell display has been found.
 

NARAYANK991

Excel Ninja
Hi ,

Sorry , but things are still unclear.

An Excel formula is entered in a cell , so that it displays the result of the formula in that cell.

For example , A1 can have a formula , which displays one thing if the conditions are satisfied , and another thing if the conditions are not satisfied.

I am not able to understand what you mean by Excel should jump to the next row.

If you could upload your workbook , everything would be clear.

Narayan
 

kerstin boelsen

New Member
I am aware that formula display content / value of a cell, however, if I want Excel to check certain conditions are met before displaying the content of a defined cell, it should also be possible to tell excel that " if these 2 cells do not meet the condition, jump to the next row and check if the defined 2 cells there met the conditions" . example file attached. SO Iam not talking about A1 meeting a condition, but A1 and B1 need to meet a certain condition to display F1. if A1 or B1 do not display the condition, then F1 is not to be displayed and the next row shall be checked if A2 and B2 meet the condition so F2 can be displayed. The "display tab" is only to show cells of rows with A& B cells which meet the conditions.
 

Attachments

kerstin boelsen

New Member
This looks like it :) I never thought of index :( however, when I try to repeat the formula, it will continue to only display the result from the first matched row.
 
Last edited:

NARAYANK991

Excel Ninja
Hi ,

If you want a proper solution , there are two ways to go about it :

1. Give a clear , complete description of the requirement , including details about all the variations in input data , and how they need to be handled.

2. Upload a workbook , which has enough data , say 50 or even 100 rows , so that all possible variations are available.

Narayan
 

kerstin boelsen

New Member
Sorry Narayan, I am not sure how I can explain more detailed what it is I'm looking for: source tab cell condition: if B= EMEA CENTRAL, and Cell F= Digital marketing, then display content of cell O [in that row) ; if B and F do not match condition, check the following row if conditions are met.
If cell B1 = EMEA central, Cell F1= digital media (=condition not met), go to row 2 to check if B2 = EMEA central and F2= digital marketing; if F2= EMEA central and F2 = digital marketing then display content of source tab O2 in cell C2 of Display tab (if not then go to row 3 on source tab [...]), if if F3= EMEA central and F3 = digital marketing then display content of source tab O3 in cell C3 of Display tab (if not then go to row 4 on source tab [...]).
The goal is to have a search displaying the content of Parent Account name from cell O on source tab in cell C on display tab and excluding all Parent account names in rows which do not meet the criteria.
 

Attachments

NARAYANK991

Excel Ninja
Can you please explain the role of Row(A1) in the formula?
Hi ,

The SMALL function or the LARGE function has the following syntax :

=SMALL(range , k)

where range is a single or multi-row , single or multi-column reference , and k is an integer taking the values 1 , 2 , 3 ,...

Depending on the value of k , the SMALL function will return the smallest , the second smallest , the third smallest value in a range.

However , when you use the SMALL function in a formula , where the formula is entered in one cell and then copied either across columns or down rows , then if you have the value 1 in the formula , that value will be copied to all other cells. Thus , if you want the first cell to contain the smallest value , the second cell to contain the second smallest value , the third cell to contain the third smallest value and so on , you will not get what you want. Every cell will contain the same value.

Thus , if you want k to change from 1 to 2 to 3 and beyond , then there to be a variable in k , not a fixed value such as 1 or 2 or 3.

This is the reason we use the ROW function , with a parameter such as A1.

=ROW(A1) returns 1

=ROW(A2) returns 2

=ROW(A3) returns 3

and so on.

When we have a formula such as :

=SMALL($J$7:$J$19 , ROW(A1))

entered in a cell say D3 , and copy this formula down to cells D4 , D5 , D6 and beyond , then the formulas in those cells will be :

D3 : =SMALL($J$7:$J$19 , ROW(A1))

D4 : =SMALL($J$7:$J$19 , ROW(A2))

D5 : =SMALL($J$7:$J$19 , ROW(A3))

D6 : =SMALL($J$7:$J$19 , ROW(A4))

So because the k value changes , the result of the formula will also change , thus giving us what we want.

Another way of changing the k value is to use the ROWS function , as follows :

D3 : =SMALL($J$7:$J$19 , ROWS(A$1:A1))

D4 : =SMALL($J$7:$J$19 , ROWS(A$1:A2))

D5 : =SMALL($J$7:$J$19 , ROWS(A$1:A3))

D6 : =SMALL($J$7:$J$19 , ROWS(A$1:A4))

=ROWS(A1:A1) returns the number of rows within the range A1:A1 ; the result is 1.

=ROWS(A2:A2) returns the number of rows within the range A2:A2 ; the result is still 1.

=ROWS(A99:A99) returns the number of rows within the range A99:A99 ; the result is still 1.

If we prefix the first row reference with the $ symbol to make it absolute , and leave the second row reference relative (without the $ symbol) , then when we copy the formula downwards , the first row reference will remain unchanged , while the second will change from one cell to the next.

=ROWS(A$1:A2) returns the number of rows within the range A1:A2 ; the result is 2.

=ROWS(A$1:A3) returns the number of rows within the range A1:A3 ; the result is 3.

=ROWS(A$1:A4) returns the number of rows within the range A1:A4 ; the result is 4.

If you have any doubts , feel free to ask.

Narayan
 

pirya93

New Member
Thank you so much for your reply.

I downloaded your file where you have showed this formula, and I am trying to mimic it exactly except the cell references. It is only giving the FALSE value. Could you please help me with that?

Regards.
Pirya
 

pirya93

New Member
Hi ,

The SMALL function or the LARGE function has the following syntax :

=SMALL(range , k)

where range is a single or multi-row , single or multi-column reference , and k is an integer taking the values 1 , 2 , 3 ,...

Depending on the value of k , the SMALL function will return the smallest , the second smallest , the third smallest value in a range.

However , when you use the SMALL function in a formula , where the formula is entered in one cell and then copied either across columns or down rows , then if you have the value 1 in the formula , that value will be copied to all other cells. Thus , if you want the first cell to contain the smallest value , the second cell to contain the second smallest value , the third cell to contain the third smallest value and so on , you will not get what you want. Every cell will contain the same value.

Thus , if you want k to change from 1 to 2 to 3 and beyond , then there to be a variable in k , not a fixed value such as 1 or 2 or 3.

This is the reason we use the ROW function , with a parameter such as A1.

=ROW(A1) returns 1

=ROW(A2) returns 2

=ROW(A3) returns 3

and so on.

When we have a formula such as :

=SMALL($J$7:$J$19 , ROW(A1))

entered in a cell say D3 , and copy this formula down to cells D4 , D5 , D6 and beyond , then the formulas in those cells will be :

D3 : =SMALL($J$7:$J$19 , ROW(A1))

D4 : =SMALL($J$7:$J$19 , ROW(A2))

D5 : =SMALL($J$7:$J$19 , ROW(A3))

D6 : =SMALL($J$7:$J$19 , ROW(A4))

So because the k value changes , the result of the formula will also change , thus giving us what we want.

Another way of changing the k value is to use the ROWS function , as follows :

D3 : =SMALL($J$7:$J$19 , ROWS(A$1:A1))

D4 : =SMALL($J$7:$J$19 , ROWS(A$1:A2))

D5 : =SMALL($J$7:$J$19 , ROWS(A$1:A3))

D6 : =SMALL($J$7:$J$19 , ROWS(A$1:A4))

=ROWS(A1:A1) returns the number of rows within the range A1:A1 ; the result is 1.

=ROWS(A2:A2) returns the number of rows within the range A2:A2 ; the result is still 1.

=ROWS(A99:A99) returns the number of rows within the range A99:A99 ; the result is still 1.

If we prefix the first row reference with the $ symbol to make it absolute , and leave the second row reference relative (without the $ symbol) , then when we copy the formula downwards , the first row reference will remain unchanged , while the second will change from one cell to the next.

=ROWS(A$1:A2) returns the number of rows within the range A1:A2 ; the result is 2.

=ROWS(A$1:A3) returns the number of rows within the range A1:A3 ; the result is 3.

=ROWS(A$1:A4) returns the number of rows within the range A1:A4 ; the result is 4.

If you have any doubts , feel free to ask.

Narayan

I wish to add formula in B4. Condition that if H4 and I4 both are zero, skip the row and move to next result. It is important to have 0 in both cells. If one cell has zero and other hasn't, row should not move.

please help.

Regards.
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

I am not able to understand how a formula can be added in cell B4 , which already has data in it.

Can you please mention which worksheet is involved ?

Narayan
 

pirya93

New Member
Hi ,

I am not able to understand how a formula can be added in cell B4 , which already has data in it.

Can you please mention which worksheet is involved ?

Narayan
Sorry, I guess I attached the wrong file

Sheet named Bendigo is involved.

I wish to add formula in B4. Condition that if H4 and I4 both are zero, skip the row and move to next result. It is important to have 0 in both cells. If one cell has zero and other hasn't, row should not move.

For example: For Alanine, if H4 = 0 and I4 = 0, Alanine should not be in the table

Regards.
pirya
 

Attachments

pirya93

New Member
Hi ,

See the attached file.

Note that the formula using the SMALL function is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan



Thank you so much :)
That worked. :)
I have rather some questions:

If the curly bracket gets removed anyhow, the formula wouldn't work?
How to go about it?
Can I drag it down?
We should press CTRL SHIFT ENTER. at the last of formula (When we are done writing it) or anywhere in middle?

Please guide.

Thanks a lot again.
 

NARAYANK991

Excel Ninja
Hi ,

For a normal non-array formula , you type in the formula and press the ENTER key.

For an array formula , after typing in , you don't press only the ENTER key ; instead you press the 3 key combination of CTRL SHIFT ENTER ; when you do this , Excel automatically inserts the curly brackets { and } around the formula. These cannot be entered by the user.

Any formula can be dragged down.

Yes , if a cell contains an array formula and you press the F2 key to edit it and then press only the ENTER key , it is now no longer going to function as an array formula ; its results will be wrong.

Narayan
 

pirya93

New Member
Hi ,

For a normal non-array formula , you type in the formula and press the ENTER key.

For an array formula , after typing in , you don't press only the ENTER key ; instead you press the 3 key combination of CTRL SHIFT ENTER ; when you do this , Excel automatically inserts the curly brackets { and } around the formula. These cannot be entered by the user.

Any formula can be dragged down.

Yes , if a cell contains an array formula and you press the F2 key to edit it and then press only the ENTER key , it is now no longer going to function as an array formula ; its results will be wrong.

Narayan


That makes sense :)
Thanks a lot

Pirya.
 
Top