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

Conditional statement

Chandra Mouli

New Member
From a range of values, if all the values are the same print result as "Yes" else result as "No" printed in a cell
 

Attachments

  • Doc1.xlsx
    11.5 KB · Views: 6
Hi ,

What is the meaning of range of values ?

You have data in the range C4:C11 ; will there be only one output for this range of values , or will there be an output for every cell in this range ?

Can you upload a workbook which has the manually worked out outputs in the range D4:D11 ?

C5 is different from the other values ; what will be the output given that this cell is different from the others ?

Narayan
 
Hi Narayan,

Thanks for your quick response!

Yes, for the data in the range C4:C11, I need only one output for the given range of values.

The condition is the output should be "XXX" if all the values are same, else the output be "YYY" if at least one value in the range is different from the others.

Hope I am clear now.


Chan Li
 
If you are comfortable with array formulas
=AND(List=INDEX(List,1))
will test every item of the list against the first item and return TRUE only if they all match.
= IF(test,"Yes","No")
would convert the TRUE/FALSE test to give "Yes"/"No".
If you prefer not to use array formulas you could count the number of times the first item is repeated in the list and compare it with the length of the list
= COUNTIF(List, INDEX(List,1))=COUNTA(List)
 
@Chandra Mouli
Thank you for letting me know. There is one further trick if you are familiar with Name Manager. If you declare 'test' as a new Name and place the formula in its 'Refers to' dialogue box, any further formula that uses 'test' will evaluate it as an array, so CSE is no longer required!
 
@Chandra Mouli
Thank you for letting me know. There is one further trick if you are familiar with Name Manager. If you declare 'test' as a new Name and place the formula in its 'Refers to' dialogue box, any further formula that uses 'test' will evaluate it as an array, so CSE is no longer required!

Hey Peter!

How are you doing!

Need your help on the previous problem...

Have used the Name Manager and created the list, however, I got stuck up with no desired result using the array formula.

Could you please go through the attached file and provide me with a solution.
 

Attachments

  • Problem-1.xlsx
    9.1 KB · Views: 3
Hi Chandra
I have built upon your workbook to show the formula being built from the most basic steps using names. The starting point is the green heading 'List' that matches any one of the 3 cases (via the use of option buttons)
The numbered stages gradually build your formula, though stage 3 offers distinct strategies of using helper cells, nested formulae or a named formula. The first two options are also relevant to direct referencing. This leads to solutions 4A, 4B, or 4C. I tend to use 4C but you will have your own style.
Because your original post asked for a consistency test, I have provided that as formula 5 (top right).
I hope by going through in small steps the ideas are made clear. The same issues arise with direct referencing so I have also shown the formula and a single-cell array formula using direct references.
Peter
 

Attachments

  • Problem-Solutions.xlsx
    17.6 KB · Views: 5
Hey Peter!

How are you doing!

Need your help on the previous problem...

Have used the Name Manager and created the list, however, I got stuck up with no desired result using the array formula.

Could you please go through the attached file and provide me with a solution.
Try,

In E4, enter formula and copied to I4

=IF(COUNTIF(C4:C13,"Corr")>0,"Corr","Approved")

Regards
 
Back
Top