Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
On sheet X
Cell A1 either displays an "S" or an "R". ( this comes in when the numbers in B1 thru B16 is downloaded. On sheet X Cells B1 thru B16 will display from 6 to 16 numbers in any random order ex. 3-4-7-1-9-5-16-2, , , . Note: They are never the same or is there ever a duplication of a single number in the same set of 6 to 16. The data set comes in ten times a day 9 different sets at a time. This part of the display is complete. This is what I need, , , On sheet Z Cell Q1 needs to display If A1 = S then display the first four numbers in B1:B16 ex. 1,2,3,4 ( this is an easy IF statement but the next one if not an S but is an R has me wondering ) If A1 = R then display the FIRST THREE AND THE LAST THREE NUMBERS in B1:B16 ex. 1,2,3 / 4,5,6 or 1,2,3 / 14,15,16 depending on length of number values Can anyone supply me with a formula for cell Q1 on sheet Z to do this? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
you can use a nested IF
=IF(A1="S", LEFT(B1, 4), IF(A1="R", LEFT(B1, 3) & RIGHT(B1, 3), "A1 is neither an S nor an R")) "Shu of AZ" wrote: On sheet X Cell A1 either displays an "S" or an "R". ( this comes in when the numbers in B1 thru B16 is downloaded. On sheet X Cells B1 thru B16 will display from 6 to 16 numbers in any random order ex. 3-4-7-1-9-5-16-2, , , . Note: They are never the same or is there ever a duplication of a single number in the same set of 6 to 16. The data set comes in ten times a day 9 different sets at a time. This part of the display is complete. This is what I need, , , On sheet Z Cell Q1 needs to display If A1 = S then display the first four numbers in B1:B16 ex. 1,2,3,4 ( this is an easy IF statement but the next one if not an S but is an R has me wondering ) If A1 = R then display the FIRST THREE AND THE LAST THREE NUMBERS in B1:B16 ex. 1,2,3 / 4,5,6 or 1,2,3 / 14,15,16 depending on length of number values Can anyone supply me with a formula for cell Q1 on sheet Z to do this? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Verge,
I think what he wants to do is to concatenate the various numbers in the cells B1 through B16 when the value in A1 is either S or R, rather than working on first/last cells in any one of those cells. What I've come up with is below. It depends on a couple of things to work properly: That there always be at least 3 cells in B1:B16 used, and that there are not any empty cells in those lists until the last entry is encountered. By that I mean that you can't have 6 numbers, but have them in B1:B3 and B5:B7, with B6 empty. It looks ugly, but seems to work: =IF(X!A1="S",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " | "),IF(X!A1="R",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " | ",OFFSET(X!B1,COUNT(X!B$1:B$16)-3,0)," | ", OFFSET(X!B1,COUNT(X!B$1:B$16)-2,0), " | ", OFFSET(X!B1,COUNT(X!B$1:B$16)-1,0)),"Neither S nor R")) I used the | symbol as a separator to try to help keep the confusion down about where the commas are that separate the CONCATENATE parameters and the character to be used to separate the individual cell results that are being concatenated. Hopefully one of us is right in our attempt to help Shu. "Vergel Adriano" wrote: you can use a nested IF =IF(A1="S", LEFT(B1, 4), IF(A1="R", LEFT(B1, 3) & RIGHT(B1, 3), "A1 is neither an S nor an R")) "Shu of AZ" wrote: On sheet X Cell A1 either displays an "S" or an "R". ( this comes in when the numbers in B1 thru B16 is downloaded. On sheet X Cells B1 thru B16 will display from 6 to 16 numbers in any random order ex. 3-4-7-1-9-5-16-2, , , . Note: They are never the same or is there ever a duplication of a single number in the same set of 6 to 16. The data set comes in ten times a day 9 different sets at a time. This part of the display is complete. This is what I need, , , On sheet Z Cell Q1 needs to display If A1 = S then display the first four numbers in B1:B16 ex. 1,2,3,4 ( this is an easy IF statement but the next one if not an S but is an R has me wondering ) If A1 = R then display the FIRST THREE AND THE LAST THREE NUMBERS in B1:B16 ex. 1,2,3 / 4,5,6 or 1,2,3 / 14,15,16 depending on length of number values Can anyone supply me with a formula for cell Q1 on sheet Z to do this? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
OOPS!! My "COUNT" should have been COUNTA in all instances, so this works
just a little better <g =IF(X!A1="S",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " | "),IF(X!A1="R",CONCATENATE(X!B1," | ",X!B2, " | ", X!B3, " | ",OFFSET(X!B1,COUNTA(X!B$1:B$16)-3,0)," | ", OFFSET(X!B1,COUNTA(X!B$1:B$16)-2,0), " | ", OFFSET(X!B1,COUNTA(X!B$1:B$16)-1,0)),"Neither S nor R")) "Vergel Adriano" wrote: you can use a nested IF =IF(A1="S", LEFT(B1, 4), IF(A1="R", LEFT(B1, 3) & RIGHT(B1, 3), "A1 is neither an S nor an R")) "Shu of AZ" wrote: On sheet X Cell A1 either displays an "S" or an "R". ( this comes in when the numbers in B1 thru B16 is downloaded. On sheet X Cells B1 thru B16 will display from 6 to 16 numbers in any random order ex. 3-4-7-1-9-5-16-2, , , . Note: They are never the same or is there ever a duplication of a single number in the same set of 6 to 16. The data set comes in ten times a day 9 different sets at a time. This part of the display is complete. This is what I need, , , On sheet Z Cell Q1 needs to display If A1 = S then display the first four numbers in B1:B16 ex. 1,2,3,4 ( this is an easy IF statement but the next one if not an S but is an R has me wondering ) If A1 = R then display the FIRST THREE AND THE LAST THREE NUMBERS in B1:B16 ex. 1,2,3 / 4,5,6 or 1,2,3 / 14,15,16 depending on length of number values Can anyone supply me with a formula for cell Q1 on sheet Z to do this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|