CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
eagle
eagle

Reputation: 843

How to get data from specific sequence (non-continuous) of cells and apply formulas on them

I have a very wide (width-wise like up to column HK) sheet, and I want to select a sequence (non-continuous) of cells and then want to apply different formulas on them. While mostly, formulas need a continuous range of cells like A2:2, A5:5, etc.

Here is my example enter image description here 1st question: I want to select K101, Q101, W101, AC101, ...., FK101 and then want to apply different complex formulas like

2nd question: Then I want to select (non-continuous) sequence K104, Q104, W104, AC104, ...., FK104 and then want to find the minimum and maximum values separately, while the selected cells have text too.

Similarly, I need to apply similar formulas to similar (non-continuous) sequences, like L101, R101, X101, AD101, ...., FL101, and so on.

Is there any easy solution available for this problem?

EDIT: Both questions are linked to each other, so asked both in one.

Upvotes: -2

Views: 56

Answers (2)

Tedinoz
Tedinoz

Reputation: 8069

You have a non-contiguous range (K101, Q101, W101, AC101, ...., FK101) and you want to calculate the minimum value greater than 0 (zero).

Try this formula:

=small(
  map(
    sequence(27,1,11,6),
    lambda(
      colnum,if(indirect(address(101,colnum,4))>0,indirect(address(101,colnum,4)),)
    )
  )
,1)

The formula can be adapted to perform different complex formulas.

  • SMALL: like MIN but works on a data set (in this case an array of values)
  • SEQUENCE(27,1,11,6): returns an array of numbers equal to the column numbers for each of the non-contiguous cells.
    • there are 27 non-contiguous cells
    • the starting column ("K") is column 11
    • each non-contiguous cell is separated by 6 columns
  • ADDRESS(101,colnum,4): returns the address of each of the non-contiguous cells
    • data values are on row 101
    • SEQUENCE(27,1,11,6) (named 'colnum' within "Lambda") supplies the column number
  • INDIRECT(ADDRESS(101,colnum,4)): returns the value of the respective cell address
  • IF: returns only the values greater than zero

RESULTS

Note: the value in row 101 is the respective column number.

Col Number Cell address Values (raw) Values >0 Answer
SEQUENCE ADDRESS INDIRECT IF>0 SMALL
11 K101 11 11 11
17 Q101 17
23 W101 23 23
29 AC101 29 29
35 AI101 35 35
41 AO101 41 41
47 AU101 47 47
53 BA101 53 53
59 BG101 59 59
65 BM101 65 65
71 BS101 71 71
77 BY101 77 77
83 CE101 83 83
89 CK101 89 89
95 CQ101 95 95
101 CW101 101 101
107 DC101 107 107
113 DI101 113 113
119 DO101 119 119
125 DU101 125 125
131 EA101 131 131
137 EG101 137 137
143 EM101 143 143
149 ES101 149 149
155 EY101 155 155
161 FE101 161 161
167 FK101 167 167

Upvotes: 1

Babanana
Babanana

Reputation: 1476

Working with Non-continuous ranges in Google Sheets

For starters, I would like to share with you that it is really not good practice to focus on what formula you want to use, instead of what formula can deliver your desired result. For example, your Minifs Functions, the parameters of this function cannot be changed and there is no workaround. However, Google Sheets almost covers everything and there are other ways to get the desired result. For your case you want to get a minimum value on the range that is positive non zero numbers then instead of Minifs why not use filter and min which will deliver the same result I am pretty sure that this approach will almost help you with most of your projects.

Another approach if you are open to using things like a helper column then that would also help you.

For the sake of helping you move on from the specific issue I created a formula you can use, Generally the Brackets created an array that can be accepted on a Filter where you can remove zeroes and negative numbers then Min Function to get the minimal value. It is also good to note that you can think of the filter condition as your condition in the ifs.

Sample Formula

=MIN(FILTER({A13,C13:J13,L13:N13,P13:R13},{A13,C13:J13,L13:N13,P13:R13}>0))

or you can use the Let function to make everything easier to work on.

=LET(x,{A13,C13:J13,L13:N13,P13:R13}, MIN(FILTER(x,x>0)))

You can use let to assign a variable on the non- continuous range so you just need to write it once then just call the variable within the formula just like in the example.

References:

Let Function

Filter Function

Min Function

Upvotes: 0

Related Questions