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
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Conditional statement
- Thread starter Chandra Mouli
- Start date
NARAYANK991
Excel Ninja
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
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
Chandra Mouli
New Member
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
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
Peter Bartholomew
Well-Known Member
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)
=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
New Member
Thank you Peter!
I have tried with array formula given by you. Got the desired result!
Will get back if I need any support.
I have tried with array formula given by you. Got the desired result!
Will get back if I need any support.
Peter Bartholomew
Well-Known Member
@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!
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
New Member
@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
Peter Bartholomew
Well-Known Member
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
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
Try,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.
In E4, enter formula and copied to I4
=IF(COUNTIF(C4:C13,"Corr")>0,"Corr","Approved")
Regards