Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel counts 2 digit numbers as 1...
Hi,
How do I get around Excel counting the first digit in 2 digit numbers, for example 10, as 1 when I have added a SUMIF<= function. Example: I have 12 different columns, each named TX1, TX2 etc. And a formula that extracts from a different tab and counts the value if it is <= (less than or equal to) the current column. When Excel reaches to columns with 2 digit numbers it starts counting the 1 in the name TX10, and the 1 in the name TX11. How do I get around this? -- Brile |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel counts 2 digit numbers as 1...
I don't quite understand this - perhaps you could post examples of
your data and the formulae you are using. Pete On Oct 3, 12:27*pm, Brile wrote: Hi, How do I get around Excel counting the first digit in 2 digit numbers, for example 10, as 1 when I have added a SUMIF<= function. Example: I have 12 different columns, each named TX1, TX2 etc. And a formula that extracts from a different tab and counts the value if it is <= (less than or equal to) the current column. When Excel reaches to columns with 2 digit numbers it starts counting the 1 in the name TX10, and the 1 in the name TX11. How do I get around this? -- Brile |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel counts 2 digit numbers as 1...
Its because Excel is treating the TX1, TX2 as text and TX11 is less than TX2
(sort the column ascending to see the comparison sequence). You can either make all the TX1 to TX9 into TX01 to TX09 (make all the TX strings the same length) or use a helper column (C) on Sheet 2 to extract the numbers =VALUE(RIGHT(A1,LEN(A1)-2)) and then change your SUMIF to =SUMIF(Sheet2!$C$1:$C$30,"<=" & RIGHT(Sheet1!A1,LEN(A1)-2),Sheet2!$B$1:$B$30) regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Brile" wrote in message ... Hi, How do I get around Excel counting the first digit in 2 digit numbers, for example 10, as 1 when I have added a SUMIF<= function. Example: I have 12 different columns, each named TX1, TX2 etc. And a formula that extracts from a different tab and counts the value if it is <= (less than or equal to) the current column. When Excel reaches to columns with 2 digit numbers it starts counting the 1 in the name TX10, and the 1 in the name TX11. How do I get around this? -- Brile |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel counts 2 digit numbers as 1...
Hi Pete,
thanks for answering, I do not know how well you know Excel but if you need further explainations please ask, I am VERY grateful for help! Below is my formula, =SUMIF('Jira Reference'!$J:$J;"<="&L5;'Jira Reference'!$L:$L)/3600 The 'Jira Reference' is the Tab name which I am reffering to. I have in my first Tab several columns named TX1, TX2 etc. They should with my formula retrieve data from one column in the Tab 'Jira Reference'. The column in the 'Jira Reference' includes data called TX1, TX2 etc. and the function should use less than or equal to sum up for example in TX9 also the TX8, TX7, TX6 data etc. And it does, but when Excel comes to compare data for TX10 (2 digits 1 and 0), it does not, it interpretate it as the first digit, 1, and only counts TX1 and TX0 as less than or equal to. If I am unclear let me know again, as I said very thankful for help. :-) PS. I do not know how to post an excel file for example here on the forum? DS. -- Brile "Pete_UK" wrote: I don't quite understand this - perhaps you could post examples of your data and the formulae you are using. Pete On Oct 3, 12:27 pm, Brile wrote: Hi, How do I get around Excel counting the first digit in 2 digit numbers, for example 10, as 1 when I have added a SUMIF<= function. Example: I have 12 different columns, each named TX1, TX2 etc. And a formula that extracts from a different tab and counts the value if it is <= (less than or equal to) the current column. When Excel reaches to columns with 2 digit numbers it starts counting the 1 in the name TX10, and the 1 in the name TX11. How do I get around this? -- Brile |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel counts 2 digit numbers as 1...
Hi Charles,
Thanks for the help, it is working out fine actually. Have a great weekend! -- Brile "Charles Williams" wrote: Its because Excel is treating the TX1, TX2 as text and TX11 is less than TX2 (sort the column ascending to see the comparison sequence). You can either make all the TX1 to TX9 into TX01 to TX09 (make all the TX strings the same length) or use a helper column (C) on Sheet 2 to extract the numbers =VALUE(RIGHT(A1,LEN(A1)-2)) and then change your SUMIF to =SUMIF(Sheet2!$C$1:$C$30,"<=" & RIGHT(Sheet1!A1,LEN(A1)-2),Sheet2!$B$1:$B$30) regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Brile" wrote in message ... Hi, How do I get around Excel counting the first digit in 2 digit numbers, for example 10, as 1 when I have added a SUMIF<= function. Example: I have 12 different columns, each named TX1, TX2 etc. And a formula that extracts from a different tab and counts the value if it is <= (less than or equal to) the current column. When Excel reaches to columns with 2 digit numbers it starts counting the 1 in the name TX10, and the 1 in the name TX11. How do I get around this? -- Brile |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel counts 2 digit numbers as 1...
Hi,
Do you possibly have the answer to my other posting "Add several functions in one formula"? -- Brile "Charles Williams" wrote: Its because Excel is treating the TX1, TX2 as text and TX11 is less than TX2 (sort the column ascending to see the comparison sequence). You can either make all the TX1 to TX9 into TX01 to TX09 (make all the TX strings the same length) or use a helper column (C) on Sheet 2 to extract the numbers =VALUE(RIGHT(A1,LEN(A1)-2)) and then change your SUMIF to =SUMIF(Sheet2!$C$1:$C$30,"<=" & RIGHT(Sheet1!A1,LEN(A1)-2),Sheet2!$B$1:$B$30) regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "Brile" wrote in message ... Hi, How do I get around Excel counting the first digit in 2 digit numbers, for example 10, as 1 when I have added a SUMIF<= function. Example: I have 12 different columns, each named TX1, TX2 etc. And a formula that extracts from a different tab and counts the value if it is <= (less than or equal to) the current column. When Excel reaches to columns with 2 digit numbers it starts counting the 1 in the name TX10, and the 1 in the name TX11. How do I get around this? -- Brile |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel rounding my 16/17 digit numbers | Excel Worksheet Functions | |||
why does excel change last digit of credit card numbers to zero? | New Users to Excel | |||
How do you display 16 digit numbers in excel ? (it keeps convert. | Excel Discussion (Misc queries) | |||
Can I change the 15-digit limit in Excel for numbers ? | Excel Worksheet Functions | |||
16 DIGIT NUMBERS IN CELL WITHOUT LAST DIGIT BEING A ZERO | Excel Worksheet Functions |