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

how to use if function in an range in excel to get the result in excel

J

jraju

Member
Hi,
i know how to use if function for general equations. But how to write formula to say, to find a particular number, and if those are present in all of them, i want to have the if function to get a result. if for example, if a1 to a6 contain 200, then i should get 150 in a7. in a single column exercise.
how to do the same if the range expands , say a1 to d20
 
Hi, I know how to use if function for ordinary formulas.

I want to know how to get one result using a range
here the range is a1 to a6 having 200 as number
now i want in a7 150 if all the cells contain the 200, otherwise, it should be left blank.
This is my first query explanation
Secondly, if the range is extended beyond one column, that is a, then how to get the result of the things, if for example, a1 to d20 contains 200, i should get 150 in some other column. I think i made my query clear and i do not think to upload a file for this purpose
 
Hi,

For your first query use below formula with data in A1:A6, Condition 200 in J1 put in A7
=IF(COUNTIF($A$1:$A$6,$J$1)=ROWS($A$1:$A$6),150,"")

For your second query, with data in A1:D20, condition 200 in J1 put in any cell
=IF(COUNTIF($A$1:$D$20,$J$1)=(ROWS($A$1:$A$20)*COLUMNS($A$1:$D$1)),150,"")

Just advise if any issue.

Regards,
 
Hi, thank you for prompt reply. But why should we use countif and also, why should there be something in J1. Would you please explain.Would not normal if function work on these kind of formule. Why i need to have 200 in j1. if i expand to include this entry in j1 may get lost. Could not have it on the formula itself.
 
Last edited:
I had put value 200 in J1 so as to make more robust, if you want to check any other value you can just change cell value, no need to change formula, If you dont want to use cell J1 than you can directly put 200 in the formula.

Use of countif is done because you are doing an IF condition on a range of cells and not on a single cell.

Regards,
 
Thank you so much for the clarification. Would you throw some light on what an {} formula. How it is different from the one you give me as solution. Thank you sir,
 
{} is normally used in formula to pass an array arguments for e.g. if any formula say SMALL() require to argument one array of numbers in which you want to find small and other k i.e. which small 1st small , 2nd small or like that. So, you can pass a range of numbers like A1:A6 in small function or like {1;2;3;4}. Even if you pass an array of range like A1:A6 and select it in edit mode and press F9 to evaluate it you will see some array like {10;20;30;40;50;60} than do the Ctrl+Z to bring the range again.

Here please note that in edit mode you can do undo action only once so if you did not undo after evaluation it will be hard coded in the formula as an array of numbers and not as range.

Secondly, in array a ; means rows and a , means columns so {1;2} means 2x1 array and {1,2} means 1x2 array.

This is about array argument now if you are passing an array of to a formula where it is seeking a single argument you have to tell excel that you want an array calculation by a special key stroke Ctrl+Shift+Enter. This is must. For e.g. IF() function like =If(A1="Yes", B1=1, B1=0) this normal function this is entered with Enter but if you do something Like =IF(A1:A6 = "YES", B1:B6,0) this is an array argument so you must enter with Ctrl+Shift+Enter.

But function which ask for array and dont require this special key stroke like SMALL, LARGE, SUMPRODUCT and others. You can find that by the screen tip of formula, there it will come as array. But if you are doing an array IF in any of these function or other function you must do Ctrl+Shift+Enter.

Formula entered with this special key stroke get surrounded by {} in formula bar. They are inserted by excel automatically. If you delete them or manuualy insert them it will not work.

By mistake, if you press Enter where you require ctrl+Shift+Enter you will Get #VALUE error.

This is just a small explantion of {} in formula. FOr better understanding of this topic you can visit Chandoo site Formula section.

Regards,
 
Thank you so much for the explanation. i tried the formula with but resulted in error.
=if(and(a1:a6=200,150,"")) but got the error answer. so, i asked the question.I even tried by cont.shit,enter, but the result is wrong. Now, if count formula gets me the correct answer.
 
This worked perfectly well. Pl point the error i have done and also, the inclusion of two ifs, and also, 1,0, so that i could further improve upon. Thannks
 
First of all the formula you posted above is Incomplete. It has only logicacal contion of IF function and no value for true or false.

The first IF is to put 150 or "" base on the condtion, seconf IF is checking for values in the range. Now 1 is treated as TRUE and 0 as FALSE. So if in the range all are 200 then we will get all 1 and AND function will see all true, so it return the value if true i.e. 150. If any one is not equal to 200 there will will get 0 i.e. FALSE for AND function so condtion in first IF goes FALSE so we will get "".

Hope this will clear your doubt.

Regards,
 
Thank you so much sm,
The error i would definitely correct in my future work. Some formula do work if the condition match, and some if it does not. Thank for putting me on the right path. How to close this thread as solved and i do not find any kudos or similar button to thank you.One well versed in mathematics would definitely give good solution. I only do automated task, so, the construction of formula and the clarification given on it needs special mention.
How to find my threads quickly in the forum
 
Last edited:
Hi jraju,

You are welcome & thanks for your kind words.

As per your queries, well I really don't know how to close a thread as Solved. May be you ask this question in forum. As far as to find your thread quickly, there are many ways
1. In search option Top right hand corner you can search by member name, just start typing name it will automatically suggest members.
2. In Search Option same as as above go to more and your thread/your post option.
3. Go to your profile page than posting tabs, scroll down completely there also you will see these options Your threads/Your post.

Regarding thanks giving there is no special button, but if you really like somebody effort in giving you a proper and efficient solution, you can hit like option.

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