Pinned Posts
Forum Widgets
Latest Discussions
Excel Paste as Image Issue
Hello, apologies if this is not the best place to post this. Whenever I special paste, as a picture, it shows my selected excel cells as a tiny top left section of the whole pasted object. I then have to crop the “image” to only show the actual section of excel cells and remove the clear components. Any assistance with this would be greatly appreciated as I havent had much luck finding this same problem online. I experience it whenever I paste from excel to powerpoint, excel to excel, or excel to powerpoint, but only as an image. Please let me know if this is not allowed on here!
Conditional Formatting for Spilled Arrays
I have recently composed a bunch of functions to make using conditional formatting with spilled arrays more manageable. You can check out those formulas at the end of my post. I am curious if anyone has a different way to accomplish this goal. I will briefly describe the overall method and give a use case. In essence, to use conditional formatting you need to 1)specify a range of cells where the rule will be conditionally applied (the "Conditional Formatting Range") and 2) specify the condition for application. In many cases, the formatting is conditioned upon the placement of the cell/column/row/etc. relative to the spilled array which generated it (the "Spilled Array Range"). The problem (so far as I know) is that Excel's conditional formatting manager does not allow dynamic range references to specify the Conditional Formatting Range, so there is a fundamental disconnect between the Conditional Formatting Range and the possible range of the Spilled Array Range you want to format. It occurred to me that one possible solution to this problem is to A) set the Conditional Formatting Range to a defined range that is certain to be larger than your Spilled Array Range and B) create conditions that inherently self limit themselves to the Spilled Array Range regardless of the size of your Conditional Formatting Range. Mindful of the perils of using volatile functions, I wanted to create a solution that avoids turning values (string references primarily) into range references via functions like indirect and offset. That meant that I was limited to selecting a subrange from a larger range and constructing a dynamic range by way of a pattern like "index(array1, starting_row, starting_col):index(array2, ending_row, ending_col)" where the first index returns a single cell range reference to the first cell of the target range and the second index returns a single cell range reference to the last cell of the target range. This idea sort of distills down to the last few lines of the function I created: result, IF( NOT(base_in_container), NA(), LET( start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col), end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col), start_ref:end_ref ) ), If you name the range created in this way, you can simply enter "=named_range" into the applies to field in the conditional formatting interface and Excel will, at the time you apply the rule, resolve that name into whatever the then current range reference is of named_range, e.g. "A$7$:$G$52". Assuming your spilled array is contained in that range, your conditional formatting rule will apply to the spilled array. I call this larger containing range something like "CF_OVERSIZED_ARRAY." Once CF_OVERSIZED_ARRAY is set to a rule, you never have to change the Conditional Formatting Range again unless your spilled array size possibly exceeds whatever range you initially selected. (For instance, if your oversized array is 50 rows and 12 columns you need not change its size unless your spilled array suddenly grows from say 7 columns to 14). The elegance of this method over directly hardcoding the value is that if you have many conditional formatting rules, by entering "=named_range" for the applies to range in each rule, you both eliminate the possibility of inconsistent application ranges and have a visual confirmation when entering each rule that the value you are setting is exactly what you intend (rather than something like "=worksheet1!$A$23:$H$79"). Furthermore, by programmatically defining the oversized array, you can make it as "small" as reasonable, thereby avoiding having conditional formatting apply to many unused cells (such as the whole worksheet). At this point, the next computation minimization occurs - a guard clause for each condition is specified such that any cell in CF_OVERSIZED_ARRAY outside of the Spilled Array Range immediately returns a false and no further condition checking is performed. The general formula for checking if a cell is within the Spilled Array Range is as follows along with an example of a guard clause: is_within_array = LAMBDA( range, LET( start_row, ROW(TAKE(range, 1, 1)), start_col, COLUMN(TAKE(range, 1, 1)), AND( ROW() >= start_row, ROW() < start_row + ROWS(range), COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range) ) ) ); is_in_row_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( Now that the basic structure has been established, a number of workhorse functions are established: is_in_row_of_array - cell is within a specified row or range of rows in the spilled array such as "is in row 1" or "is in rows 4 through 6", is_in_col_of_array - cell is within a specified column or range of columns in the spilled array such as "is in column 1" or "is in columns 4 through 6", is_in_slice_of_array - cell is with a specified contiguous portion of the spilled array such as "is between rows 5 through 7 and columns 2 through 12" is_in_interval_of_array - cell is in set of every N rows or N columns such as "is one of every other row" or "is one of every third column" is_in_recurring_band_of_rows - cell is in a recurring grouping of rows such as "is 2nd and 3rd row of every group of 4 rows" is_in_recurring_band_of_cols - cell is in a recurring grouping of columns such as "is last column of every group of 7 columns" Here is an example function: is_in_col_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_cols, COLUMNS(range), current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1), end_resolved, IF( ISOMITTED(end_index), start_resolved, IF( end_index > 0, end_index, num_cols + end_index + 1 ) ), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_col >= final_start, current_col <= final_end) ) ) ); On top of the basic structure, convenience functions are created - the names of which should indicate how they solve common formatting needs: alternate_cols alternate_rows is_in_first_row_of_recurring_band_of_rows is_in_last_row_of_recurring_band_of_rows is_in_first_col_of_recurring_band_of_cols is_in_last_col_of_recurring_band_of_cols is_in_header_of_col is_in_last_row_of_col is_in_first_data_row_of_col is_between_first_and_last_row_of_col One major benefit flowing from this design is that these conditions are composable, e.g. alternate_cols * is_in_header_of_col would give you alternating formatting on the headers of a spilled array, such as an array with empty columns between each of the substantive columns. While I do not promise that the following formulas are perfect, what I can say is that they presently permit me to write rules like this: =cf.is_in_row_of_array(ins_rep.dynamic_array, 1)*cf.alternate_cols(ins_rep.dynamic_array,FALSE) =cf.is_in_first_data_row_of_col(ins_rep.dynamic_array,9) =cf.is_between_first_and_last_row_of_col(ins_rep.dynamic_array, 9,TRUE) =cf.is_in_last_row_of_col(ins_rep.dynamic_array,9) Which effectively gives me a rule for all headers (shading with underlining and bold), the ability to set the first data cell in column 9 to be a dollar format, the last cell in column 9 to be a dollar format with a top border, and all of the other cells in column 9 to be simple integers. So something like this (imagine the first cell is shaded as described and the last cell has a top border) is what I get for column 9 of a dynamically generated and spilled array: Fees $175 175 175 175 175 175 175 175 175 $1,575 Please let me know if you have found any other ways to address the problem of the Conditional Formatting Range being disconnected from the Spilled Array Range. I'm happy to answer any questions about my method or formulas, so feel free to ask. I'd also appreciate any feedback/suggestions/improvements on my idea/formulas. Here are the complete formulas (I have them saved within Excel Labs Advanced formula environment in separate modules) // _range module create_expanded_from_subset_of_containing_range = LAMBDA( base_array, desired_height, container_array, [desired_width], LET( req_width, IF(ISOMITTED(desired_width), COLUMNS(base_array), desired_width), /* --- Resolve anchors (references, not values) --- */ base_array_first_cell, INDEX(base_array, 1, 1), base_array_first_row, ROW(base_array_first_cell), base_array_first_col, COLUMN(base_array_first_cell), container_array_first_cell, INDEX(container_array, 1, 1), container_array_first_row, ROW(container_array_first_cell), container_array_first_col, COLUMN(container_array_first_cell), container_array_rows, rows(container_array), container_array_cols, columns(container_array), idx_base_in_container_first_row, base_array_first_row - container_array_first_row +1, idx_base_in_container_first_col, base_array_first_col - container_array_first_col +1, idx_base_in_container_last_row, idx_base_in_container_first_row + desired_height - 1, idx_base_in_container_last_col, idx_base_in_container_first_col + req_width - 1, base_in_container, and( idx_base_in_container_first_row > 0, idx_base_in_container_first_row <= idx_base_in_container_last_row, idx_base_in_container_last_row <= container_array_rows, idx_base_in_container_first_col > 0, idx_base_in_container_first_col <= idx_base_in_container_last_col, idx_base_in_container_last_col <= container_array_cols ), result, IF( NOT(base_in_container), NA(), LET( start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col), end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col), start_ref:end_ref ) ), result ) ); //cf module is_within_array = LAMBDA( range, LET( start_row, ROW(TAKE(range, 1, 1)), start_col, COLUMN(TAKE(range, 1, 1)), AND( ROW() >= start_row, ROW() < start_row + ROWS(range), COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range) ) ) ); is_in_row_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_rows, ROWS(range), current_row, ROW() - ROW(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_rows + start_index + 1), end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_rows + end_index + 1)), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_row >= final_start, current_row <= final_end) ) ) ); is_in_col_of_array = LAMBDA( range, start_index, [end_index], IF( NOT(is_within_array(range)), FALSE, LET( num_cols, COLUMNS(range), current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1), end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_cols + end_index + 1)), final_start, MIN(start_resolved, end_resolved), final_end, MAX(start_resolved, end_resolved), AND(current_col >= final_start, current_col <= final_end) ) ) ); is_in_slice_of_array = LAMBDA( range, start_row, start_col, [end_row], [end_col], [include_slice_neg1_to_exclude], if( not(is_within_array(range)), FALSE, LET( final_end_row, IF(ISOMITTED(end_row), start_row, end_row), final_end_col, IF(ISOMITTED(end_col), start_col, end_col), row_match, is_in_row_of_array(range, start_row, final_end_row), col_match, is_in_col_of_array(range, start_col, final_end_col), selection, AND(row_match, col_match), mode, IF(ISOMITTED(include_slice_neg1_to_exclude), 1, include_slice_neg1_to_exclude), IF(mode = -1, NOT(selection), selection) ) ) ); is_in_interval_of_array = LAMBDA( range, row_interval, col_interval, [start_at_row], [start_at_col], [include_interval_neg1_to_exclude], if( not(is_within_array(range)), FALSE, LET( row_idx, ROW() - ROW(TAKE(range, 1, 1)) + 1, col_idx, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1, start_row, IF(ISOMITTED(start_at_row), 1, start_at_row), start_col, IF(ISOMITTED(start_at_col), 1, start_at_col), row_match, IF(row_interval <= 1, TRUE, MOD(row_idx - start_row, row_interval) = 0), col_match, IF(col_interval <= 1, TRUE, MOD(col_idx - start_col, col_interval) = 0), selection, AND(row_match, col_match), mode, IF(ISOMITTED(include_interval_neg1_to_exclude), 1, include_interval_neg1_to_exclude), IF(mode = -1, NOT(selection), selection) ) ) ); alternate_cols = lambda( array, [start_with_even_df_TRUE], is_in_interval_of_array(array,1,2,,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE)) ); alternate_rows = lambda( array, [start_with_even_df_TRUE], is_in_interval_of_array(array,2,1,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE)) ); is_in_recurring_band_of_rows = LAMBDA( range, rows_in_pattern, first_row_in_band, [band_thickness], [include_selected_df_TRUE], IF( NOT(is_within_array(range)), FALSE, LET( relative_row, ROW() - ROW(TAKE(range, 1, 1)), row_in_pattern, MOD(relative_row, rows_in_pattern) + 1, actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness), is_in_band, AND( row_in_pattern >= first_row_in_band, row_in_pattern <= (first_row_in_band + actual_thickness - 1) ), include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE), IF(include_mode, is_in_band, NOT(is_in_band)) ) ) ); is_in_first_row_of_recurring_band_of_rows = lambda( range, rows_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_rows(range, rows_in_pattern, 1, 1, include_selected_df_TRUE) ); is_in_last_row_of_recurring_band_of_rows = lambda( range, rows_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_rows(range, rows_in_pattern, rows_in_pattern, 1, include_selected_df_TRUE) ); is_in_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, first_col_in_band, [band_thickness], [include_selected_df_TRUE], IF( NOT(is_within_array(range)), FALSE, LET( relative_col, COLUMN() - COLUMN(TAKE(range, 1, 1)), col_in_pattern, MOD(relative_col, cols_in_pattern) + 1, actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness), is_in_band, AND( col_in_pattern >= first_col_in_band, col_in_pattern <= (first_col_in_band + actual_thickness - 1) ), include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE), IF(include_mode, is_in_band, NOT(is_in_band)) ) ) ); is_in_first_col_of_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_cols(range, cols_in_pattern, 1, 1, include_selected_df_TRUE) ); is_in_last_col_of_recurring_band_of_cols = LAMBDA( range, cols_in_pattern, [include_selected_df_TRUE], is_in_recurring_band_of_cols(range, cols_in_pattern, cols_in_pattern, 1, include_selected_df_TRUE) ); is_in_header_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, 1), is_in_slice_of_array(array, 1, column_no) ) ); is_in_last_row_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, -1), is_in_slice_of_array(array, -1, column_no) ) ); is_in_first_data_row_of_col = LAMBDA( array, [column_no], IF(ISOMITTED(column_no), is_in_row_of_array(array, 2), is_in_slice_of_array(array, 2, column_no) ) ); is_between_first_and_last_row_of_col = lambda( array, [column_no], [exclude_first_data_row_df_FALSE], is_in_slice_of_array( array, if(isomitted(exclude_first_data_row_df_FALSE),FALSE,exclude_first_data_row_df_FALSE)+2, if(isomitted(column_no),1,column_no), -2, if(isomitted(column_no),-1,column_no), ) );
No puedo abrir mi Excel, ningún archivo.
No puedo abrir ningún archivo de excel, se queda cargando pero no abre. Solo puedo abrirlo en modo seguro. He quitado los complementos y aún sigue sin abrir. He desinstalado e instaldo Office 365 y aún sigue sin abrir, se queda cargando. Espero que alguien pueda tener la solución a este incoveniente. Los demas programas me abren (Word, PPT...)
What is the formula for counting each number from 1to25 that hits from each drawing?
I want to calculate the daily numbers for cash25 lotto. The numbers are drawn on Monday, Tuesday, Thursday, and Friday of each week. Six number from 1 to 25 are drawn each time. I have 157 rows and 7 columns (A-G). The first column is the date and Columns B-G are each number that was drawn. I know I need to do a count in each column of the total times each number is drawn. What is the formula I should enter? Help, please!
Group Timeline chart by unique values
I have a table in an excel spreadsheet in which we plan the team's vacation days (fig.1) Note: in Brazil we can take up to 3 periods in a year - totalling 30 days - and 10 of those can be sold: you receive extra money and only take 20 days off on column B the worker puts his name, column C-D the worker inputs start and end day of each vacation period the other columns serve no purpose for the graph my question is about Then, I select A2:J13 range and create a pivot table/chart with the following fields (fig.2) My question is if there's a way to overlap the periods for a same worker (Samwise for example) Each worker should have only one line and all vacation periods be on it (because 3 years from now there would be so many lines that the graph wouldn't be nice to see the time periods each one is on vacation)Double Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n (not just a count of options =COMBIN(n, m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task. Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array. In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right. So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row. By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done! p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960sHow to summarize by date range and grouping customer items
We have a table to summarize such that data is arranged by customer within specific dates. For example, 26 th to end of month plus the next 25 days as shown in this figure. How can this be done with dynamic array functions with possibility to change date range? E.g 16 th to 15 th, or 21 st to 20 th .
Finding a "2 cell" name in anther sheet and copying data
Hello- I'm looking for a formula to enter in column D of the Main Sheet. I would like to find the "Total" in sheet: 2023 for the name in cells A&B, and copy them into the Main Sheet. The names are in a different order in each sheet. I've tried XLOOKUP, but couldn't make it work. Any ideas?
