Excel formula wizards... chime in.....
JohnnyBiggles
Member Posts: 273
in Off-Topic
Two questions:
1. Is there some formula to concatenate all cells in a row (bottom row of 100+ columns) that are NOT blank? Entering a sub-formula into the concatenate formula for every single column is going to be tedious if not impossible, since the # of columns may vary and I need this to be as dynamic as possible.
2. Let's say you have 20 columns of values (numbers), and a sum for each column in the cell following the last value entered in each column. Then, in a cell below that (in each column), you have a formula to qualify that sum which would repeat that sum in this cell (example: if any of the sums are 101 or greater, across the board, the sum would output again in this cell, if the sum is 100 or less, it would be left blank). This would mean that the number of qualifying sums can vary from 0 to 20.
Then, you have another group of summary cells somewhere at the bottom of your worksheet (or on another worksheet) for printing final results, and you want to display all the qualifying sum values in size order (either lowest to highest or vice versa) with one concatenated cell.
I know you can output the highest value of a range, the lowest, and 6th highest/lowest, 5th highest/lowest, etc. or any combination of them using the MIN, MAX, SMALL, LARGE & CONCATENATE formulas (in this case, the range/row containing the qualifying sums)... but from what I understand, you must know how many entries there are to use the SMALL & LARGE formulas [example: MIN(b25:f25,6) will yield the sixth lowest value in that range/row], otherwise it will show "#VALUE" if you use a number higher than however many qualifying sums there were (if there were only 6 qualifying sums, and you used 'MIN(b25:f25,7)', the output would display '#VALUE', since a 7th lowest value does not exist). The values to get the sums will change, so the number of qualifying sums will also change, so I can't use a static formula to determine the Xth lowest/highest qualifying sum. Is there some way to make this dynamic? (*Keep in mind this is an example. What I need this for is much larger, which is why I need this to work)
I hope SOMEONE can understand what I'm trying to do here.....
1. Is there some formula to concatenate all cells in a row (bottom row of 100+ columns) that are NOT blank? Entering a sub-formula into the concatenate formula for every single column is going to be tedious if not impossible, since the # of columns may vary and I need this to be as dynamic as possible.
2. Let's say you have 20 columns of values (numbers), and a sum for each column in the cell following the last value entered in each column. Then, in a cell below that (in each column), you have a formula to qualify that sum which would repeat that sum in this cell (example: if any of the sums are 101 or greater, across the board, the sum would output again in this cell, if the sum is 100 or less, it would be left blank). This would mean that the number of qualifying sums can vary from 0 to 20.
Then, you have another group of summary cells somewhere at the bottom of your worksheet (or on another worksheet) for printing final results, and you want to display all the qualifying sum values in size order (either lowest to highest or vice versa) with one concatenated cell.
I know you can output the highest value of a range, the lowest, and 6th highest/lowest, 5th highest/lowest, etc. or any combination of them using the MIN, MAX, SMALL, LARGE & CONCATENATE formulas (in this case, the range/row containing the qualifying sums)... but from what I understand, you must know how many entries there are to use the SMALL & LARGE formulas [example: MIN(b25:f25,6) will yield the sixth lowest value in that range/row], otherwise it will show "#VALUE" if you use a number higher than however many qualifying sums there were (if there were only 6 qualifying sums, and you used 'MIN(b25:f25,7)', the output would display '#VALUE', since a 7th lowest value does not exist). The values to get the sums will change, so the number of qualifying sums will also change, so I can't use a static formula to determine the Xth lowest/highest qualifying sum. Is there some way to make this dynamic? (*Keep in mind this is an example. What I need this for is much larger, which is why I need this to work)
I hope SOMEONE can understand what I'm trying to do here.....