Checking Column Entries

[gtb]

How would you check all values in a column, real numbers, to see if they are
all the same or if any differ. Not counting blanks?

 

[Tom Hutchins]

This approach, using conditional formatting, might work for you. Select the
entire column (column A, for example), then select Format >> Conditional
Formatiing (in Excel 2003). Change the dropdown box to "Formula Is". In the
adjoining text box, enter this formula:

=AND(LEN(A1)>0,A1<>MODE($A$1:$A$60000))

Click the Format button and select the formatting you want to highlight the
cells which are not the same as all the others. Click OK, then OK.

MODE doesn't accept whole-column arguments.

Hope this helps,

Hutch

 

[ShaneDevenshire]

Hi,

=COUNTIF(A:A,A1)=COUNT(A:A)

Assume that A1 is the first non-blank cell. If not

=COUNTIF(A1:A24,MAX(A1:A24))=COUNT(A1:A24)

 

[gtb]

Say A1:A4 are user input cells, start out as blanks. Say user inputs value
4.05 in any three of the cells and leaves the fourth blank. Check if all
values in the 4 cells are the same (not carrying about blanks or location
within the 4 cells). If true return value 4.05 in cell B1, if all values in
A1:A4 are not the same (disregarding blanks) return "Text" in B1????

 

[ShaneDevenshire]

Hi,

I'm not going to ask why you would return "Text" if three cells all contain
numbers, but different number.

The formula you want is

=IF(COUNTIF(G1:G12,MAX(G1:G12))=COUNT(G1:G12),MAX(G1:G12),"text")

I continued using the same range as in the previous example.

If this helps, please click the Yes button.

 

[ShaneDevenshire]

Hi,

Here is a shorter way:

=IF(MAX(G1:G7)=MIN(G1:G7),MAX(G1:G7),"Text")

 

[gtb]

Thanks Shane, works great. Can Excel also pick up the formating for
Max(G1:G7) say the values are in inches and you want the inches " to show up
in the returned value.

 

[ShaneDevenshire]

Hi,

Sometimes Excel automatically picks up the formatting of the referenced
cells, however, those cells must be formatted before you enter the formula.

 

[gtb]

Sorry Shane, I should have posted a more concise question.
I'm using the "concastonate?" function (&) to group several cells into one.
If a cell is formated says as 0.00"''", that is two single quotations wiithin
a set of double quotations (or maybe 0.00/'' would work also) to give the
number the inch designation, and I use & to bring that cell and others into
one, I am loosing the " from the number I brought in.

 

[gtb]

Sorry Shane, I should have posted a more concise question.
I'm using the "concastonate?" function (&) to group several cells into one.
If a cell is formated says as 0.00"''", that is two single quotations wiithin
a set of double quotations (or maybe 0.00/'' would work also) to give the
number the inch designation, and I use & to bring that cell and others into
one, I am loosing the " from the number I brought in.

I've figured out my last issue of formatting entries brought together with
the & operator. Since I had two cases, one being an actual number, and the
other condition being a text entry of "V" I used an if statement, and then
the text(value,valueformat) if the reference was not a "V" but a number.

Thanks so much for all your help