Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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 "gtb" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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) -- Thanks, Shane Devenshire "gtb" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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???? "gtb" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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. -- Thanks, Shane Devenshire "gtb" wrote: 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???? "gtb" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
Hi,
Here is a shorter way: =IF(MAX(G1:G7)=MIN(G1:G7),MAX(G1:G7),"Text") -- Thanks, Shane Devenshire "gtb" wrote: 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???? "gtb" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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" wrote: Hi, Here is a shorter way: =IF(MAX(G1:G7)=MIN(G1:G7),MAX(G1:G7),"Text") -- Thanks, Shane Devenshire "gtb" wrote: 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???? "gtb" wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
Hi,
Sometimes Excel automatically picks up the formatting of the referenced cells, however, those cells must be formatted before you enter the formula. -- Thanks, Shane Devenshire "gtb" wrote: 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" wrote: Hi, Here is a shorter way: =IF(MAX(G1:G7)=MIN(G1:G7),MAX(G1:G7),"Text") -- Thanks, Shane Devenshire "gtb" wrote: 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???? "gtb" wrote: 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
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. "ShaneDevenshire" wrote: Hi, Sometimes Excel automatically picks up the formatting of the referenced cells, however, those cells must be formatted before you enter the formula. -- Thanks, Shane Devenshire "gtb" wrote: 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" wrote: Hi, Here is a shorter way: =IF(MAX(G1:G7)=MIN(G1:G7),MAX(G1:G7),"Text") -- Thanks, Shane Devenshire "gtb" wrote: 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???? "gtb" wrote: 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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Column Entries
"gtb" wrote: 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 "ShaneDevenshire" wrote: Hi, Sometimes Excel automatically picks up the formatting of the referenced cells, however, those cells must be formatted before you enter the formula. -- Thanks, Shane Devenshire "gtb" wrote: 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" wrote: Hi, Here is a shorter way: =IF(MAX(G1:G7)=MIN(G1:G7),MAX(G1:G7),"Text") -- Thanks, Shane Devenshire "gtb" wrote: 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???? "gtb" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for specific entries in a column | Excel Discussion (Misc queries) | |||
Macro for checking duplicate entries | Excel Worksheet Functions | |||
Checking entries against a column of data | Excel Discussion (Misc queries) | |||
Checking for double entries | Excel Discussion (Misc queries) | |||
Checking for duplicate entries | Excel Discussion (Misc queries) |