Help needed with IF and COUNTIFS Formulas
Is anyone able to advise the following formula: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & V3,$D5:$D15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & V3,$H5:$H15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & V3,$L5:$L15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & V3,$P5:$P15, ">" & V3) Is there a way to simplify this? Is there a way to make this more accurate? Cells in column G & H, I & J, O & P are using the following format: =IF(C6="","",C6+E6) Cells in U4:CC4 are using the following format: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & U3,$D5:$D15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & U3,$H5:$H15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & U3,$L5:$L15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & U3,$P5:$P15, ">" & U3) Cells in U5:CC15 are using the following format: =IF(U$4>=$T5,1,"") My issue is is when I put in the three break times, the mid break comes out at a shorter time. My other issue is is that when I put in the times in row 5,6and 11, the data is coming up as a combined data in rows 5, 6 and seven on the page two. Just for reference, "page two" is the same spreadsheet. What I need to happen is that I enter in the shift start time and finish time. This then populates through to Break 1, 2 and 3. The Time entry is the time the break starts. ie: 1 hour after start of shift, 1 hour after coming back from break, etc. The break entry is the duration of the break taken. ie: 30 minutes. Once all the info is put in, the relevant "Time Block" on "Page 2" shows a 1. What is happening at the moment is that when I enter all the time data, the time blocks are not populating correctly in accordance to the entry. Basically, If I have numerous people on shiftI need the time blocks to show where I have shortfalls in shift cover and not having too many people on break at the same time. IE: Link to Live Copy: https://www.dropbox.com/scl/fi/eur1j526htu1j8a4d4290/Staff-Breaks.xlsx?rlkey=r4tm9xts4tonofpa2th2cusfw&st=nueyk0d7&dl=0 Any ideas would be greatly appreciated.
Help with a Formula
Below is a formula I created to return specified rows and columns for a sheet named Detail and in the case or column 49 detect if there are comma separated values and explode them into separate columns. The formula works when there Is not comma separated values and returns and error #VALUE! instead. Can someone help me with this formula? =LET( f, FILTER( Detail!$A:$BZ, (COUNTIF($B$16:$B$25, Detail!$BP:$BP) > 0) * (TRIM(SUBSTITUTE(Detail!$AW:$AW, CHAR(160), "")) <> "")), base, CHOOSECOLS(f, 1,3,4,5,68),split49,TRIM( TEXTSPLIT(SUBSTITUTE(CHOOSECOLS(f,49), CHAR(160), ""),",")), split53,TRIM(TEXTSPLIT(SUBSTITUTE(CHOOSECOLS(f,53), CHAR(160), ""),",")), SORT(UNIQUE(HSTACK(base, split49, split53)),1, TRUE))
How to create a multi-tiered percentage-based bar chart?
Hello, I would like to create a multi-tiered percentage-based bar chart based on the data found below (test data evidently). Column B and C add up to 100%, Column D and E add up to 100%, and lastly column F and G add up to 100%. I would like each set of variables to equate to 100% on the graph, as the source data is percentage based anyways, but all be organized in their own separate row on the graph underneath the employee name. I have included a diagram drawn in MS paint to portray the desired output (with an accompanying legend). Thanks in advance! Employee Name Number of Sales Made % Number of Sales Made from Other Competitors % Number of Calls Made % Number of Calls Made from Other Competitors % Number of Individual Employees % (always is 1) Number of competitor employees % John 28.57% 71.43% 30.00% 70.00% 16.67% 83.33% Stacy 41.67% 58.33% 38.71% 61.29% 20.00% 80.00% Richard 47.06% 52.94% 47.06% 52.94% 14.29% 85.71% Andy 42.31% 57.69% 40.48% 59.52% 10.00% 90.00%
Moving a column of text data into 3 columns of data?
I have a column of text data cells 1,2,3,4,5,6,7,8,9 and longer. I want to create 3 column of data to graph and manipulate Cell in Columns. 1,2,3 3,4,5 5,6,7 8,9,10 and longer. So i need to create 3 columns of data from 1 column of data. I am using Mac Excel 16 and I can not make this happen. I have tried all sorts of solutions. Help? Thank you,Using mode with several criteria
Hello everyone! I'm currently treating some data before I send it to do some machine learning. I have the following data: id Ano mês data_inversa dia_semana horario uf br km municipio causa_acidente tipo_acidente classificacao_acidente fase_dia sentido_via condicao_metereologica tipo_pista tracado_via uso_solo ano pessoas mortos feridos_leves feridos_graves ilesos ignorados feridos veiculos And I created a new table to summarize this data for months instead of accidents, here are the columns: BR KM Ano Mês Clima (Moda) Tipo Pista Acidentes Veículos Ilesos Feridos Leves Feridos Graves Mortes Causa (Moda) Risco What I want to do is calculate the mode of condicao_metereologica for each combination of BR, KM, Ano and Mês. But I can't wrap my head around on how I could do this. Any help is appreciated! Let me know if I made anything not as clear as it is in my head xDExcel Tools for Network & Windows
Excel Tools for Network & Windows Some time ago I already shared an earlier version of this project. Since then, I have added several new features. These tools are based on functionalities that already exist in Windows and its associated software. I have consolidated them into a single Excel-based interface, allowing all tools to be executed directly from Excel. The files are free for private use. For business or enterprise environments, a more comprehensive toolbox can be developed, enabling direct support, repair, management, monitoring, and control of users and systems. Everything can be customized according to specific requirements — the scope depends solely on the desired functionality, time investment, and budget. I appreciate any positive feedback, suggestions, or constructive tips. If this project is not of interest to you, please feel free to ignore it. Thank you, and I wish everyone happy holidays.Name Manager using for create name which is contain atleast two name.
Hello. I creating somthing in excel with tables. These tables has thier own name, and can be dynamic or not. Any way. I using list in cell to select each data from a tables column. For that I created names with the content of a column. Unfortunatly i had to make a kind of name which is contain atleast 2 columns. These can grow dynamicly. So I think a plus helper tabel is not the right sollution for that. I think about 3 kind of solution. 1. =UNIQUE( VSTACK( INDEX(Tabla1[#Data];0;MATCH("Type_name";Tabla1[#Headers];0)); INDEX(Tabla2[#Data];0;MATCH("Column_name";Tabla2[#Headers];0)) ) output #name? 2. =UNIQUE(VSTACK(Lista_A; Lista_B)) lista_A as a name output #name? 3. created a helper table with the existing names. Created another name and use the table in it. output was the elements contained by the helper tabel, and the lista_A and lista_B tables each dedicated column. So How can i create list for a cell or cells from two column which are dynamicly growing, without a user needs to touch the core modell? Best BalázsConsolidate by category
I am trying the following practice challenge in an online course. I cannot for the life of me figure out the solution for step 7. Any help would be GREATLY appreciated "The next few instructions are a bit tricky. You should not need to add any columns to achieve these tasks. These tasks are similar to what was in the Consolidate by Category (Reference) video. In that video, we consolidated sheets that had different categories and in a different order by selecting Use labels in: Left column. Consolidate by Category can also condense multiple rows with the same category down to a single row per category. When you select the references, note that you will need to select at least 2 columns. The first column will be used for the labels and the other column(s) will be consolidated using the Function that you select in the Consolidate dialog. STEP 7: Use the Consolidate tool to generate a summary of the number of tickets raised per priority for May Week 4, June Week 1 and June Week 2 (combined). Sort the consolidated data by Priority. STEP 8: Use the Consolidate tool to generate a summary of the average number of days a ticket was open per priority for May Week 4, June Week 1 and June Week 2. Sort the consolidated data by Priority and change number of decimal places to 2 (change the format, do not use a rounding function). IMPORTANT: Before the next step, make sure you delete the other references in the Consolidate tool! STEP 9: Use the Consolidate tool to generate a summary of the number of tickets given each satisfaction rating for May Week 4, June Week 1 and June Week 2. Use a COUNT function. Sort the consolidated data by Satisfaction Rating." Links to excel files https://d3c33hcgiwev3.cloudfront.net/QsR8jRaoEeiV3A7PVRrSig_43591af016a811e8a3c03fd758b931b1_W1_AdvPracticeChallenge.xlsx?Expires=1764100925&Signature=ayov~GEp79FgN-SAkfGConpKqHsqzwlbG8M0jrn02RwCODRz9HsQXaHNI8IfYvEGy6I68FiyPqLJglNKFW3MBfk4AjEdeSv4dB2NiPrQTqwPFXQOvLGs3THeY7vSJiKj31--Y93AJkiyIfjQBZZyO640jN0cXLZpKl5DXLoBaDM_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A https://d3c33hcgiwev3.cloudfront.net/_f48c8a2f4c6065a2a708dc316cbcf999_Help-Desk-April-Summary.xlsx?Expires=1764100925&Signature=EnuRHABT4F-ZT925yZKt2cZzr-HYIKEod2gx1-kObdLIzueISMkjx79svRv8ZVWwVO~Y5uteVJNvS5KvdjFh2daaNvIX9OXsw5wXcKaN6zkNqVrjLe21drPYC8JIqW36Z2G3Rxpq-eHwRyKCHlBfxu85zSuTzybcjcP9hFnQ-XA_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A https://d3c33hcgiwev3.cloudfront.net/_3ffee287bc89bcdd309bfe7d8e5b00da_Help-Desk-May.xlsx?Expires=1764100925&Signature=axvArXcDo65he42Pk13z99VoJ4HS3kpzeTEMmHOEAQ9B0ou444oPGWb4Pp65MEKasrQoMQZ9A40lr2sqXqEYexJk~vENmc9Y2dGDwiEabfxQ4liQbf58sME1cUvrom26neAH40zScJbWh7rFwNZIfI7060YRYVDw4D736pH64zY_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A https://d3c33hcgiwev3.cloudfront.net/_f0e969b80e0ab5054d797a0644bd82eb_Help-Desk-June.xlsx?Expires=1764100925&Signature=LjcstfMamG2vqJqd2vcD9hiQciNCa48ngWqswE~Al7KgjCvu6M~RHzQ65G5W~Q0dAjfCmikPvIx7j4dZuCXnKWhfE3ULqy3JuJy7EExzsVCUVubmsftcP5tqTRo6No9rtPtBid1e4qkMpyE9Zwj0ny3rJn2LDSulWNYuJNXTIBw_&Key-Pair-Id=APKAJLTNE6QMUY6HBC5A
