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

G

GN0001

Member
I have if function:


=If(B4, C4, NA())


Now I would like to put a range like C4:C11 instead C4. When I do so, function returns #Value!


How can I enter the range or use a function that my function controls or picks up a range instead of one cell (C4)?


Thanks

Guity
 
You can't put the values from a range of cells into one cell without concatenating them.


You could just use a formula of


=IF(B4,C4,NA())


and just copy down.
 
No, I don't want to put value of ranges into one cell.

What I want to do is:

When if function returns True, Instead of C4, It gives me the range of C4 to C11. Please let me know if this is not clear.

Regards,

Guity
 
It is not clear. You say ... No, I don't want to put value of ranges into one cell ..., then you say ... Instead of C4, It gives me the range of C4 to C11 ... Those two statements are contradictory.
 
Okay, let me explain it more.

When the value of B4 is true, it returns C4,

Now I want to select a range instead of one cell, when the value of "If" is true. The result doesn't have the be in a cell:


B4=True

C5=Sales

C6=1515

C7=1526

C8=1444

C9=1562

C10=1490

C11=1571

C12=1702

May be an offset function can take care of this matter.

=If(B4, Offset(C4,0,0,7,1),NA())

Please give your response.

Thank you,

Guity
 
Okay, let me explain it more.

When the value of B4 is true, it returns C4,

Now I want to select a range instead of one cell, when the value of "If" is true. The result needs to be a range. I need to control a range instead of a cell with my If function.


B4=True

C4=Sales

C5=1515

C6=1526

C7=1444

C8=1562

C9=1490

C10=1571

C11=1702


Please give your response.

Thank you,

Guity
 
Refer to my comments in your other Post about Offset: http://chandoo.org/forums/topic/offset-function


Do you want something like

=If(B4, sum(offset(c4,0,0,count(c4,c100),1),na())

this will addd up the values in C4:Cx if B4 is True, NA() if False
 
What do you want to do with the Range, SUM it, or do you want all the values in the range?


Please give an example of the results you desire
 
Hui,

Yes,I exactly entered the same function you are bringing here and you discuss it before. But I couldn't get the answer though. I will try one more time to see the result. I want the "true condition part of If function" brings back a range instead of a single cell. I used offset and for the height argument, I entered count. But it didn't work. I will try it one more time and get back to you. Thanks a lot. Guity


Kachiba,

I am making a dynamic chart. I have inserted check boxes. When a check box is selected, its value is true. Then I have entered if function. If the condition is true (it means when a check box is checked), a range will be selected and the result will be reflected in the chart. Please refer to Chandoo's dynamic charts, there is a link in chandoo's website that teaches how to make dynamic charts. Please let me know if this is not clear. Thank all for your help. Guity
 
It is possible to create a new range based on the True result with an Array formula and the offset function


=IF($A$1=1,OFFSET($B$3,0,0,5,1),"")


here I am saying that if A1=1 then get me the 5 rows from B3 to B7, this formula must be entered in a range that is at least 5 rows long with Ctrl + Shift Enter to convert it into an Array formula. It will then fill the target cells with the data from the source cells. You can then point your Chart to this range
 
You must log in or register to reply here.
Back
Top