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.
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,
Unprotect in Office Scripts
I am writing an office script that requires sorting on a sheet with the overall sheet protected and specific cells not protected. I need to be able to sort an unprotected range using an automation but the protection is restricting it even though I have "sort" allowed in the protection options. Is there a way to unprotect and then re-protect the sheet as part of the scripts? Including passwords? function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Clear auto filter on selectedSheet selectedSheet.getAutoFilter().clearCriteria(); // Custom sort on range range C9:I6001 on selectedSheet selectedSheet.getRange("C9:I6001").getSort().apply([{key: 0, ascending: true}], false, true, ExcelScript.SortOrientation.rows); // Apply values filter on selectedSheet selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 6, { filterOn: ExcelScript.FilterOn.values, values: ["1"] }); }
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 xD
Excel macro VBA issues
Hey guys. - I wrote a macro, which contain other macros using Call orders. If I running the macros separately everything ok, from the main macro one of them didn't running it's like it skipped from the list. Reason unknown. -On running I can see 4 application window get visible, and those didn't connect any kind of application. Also a Clipboard error message get visible, but I get a Clipboard cleaning macro, which several times cleaning the Clipboard, so it can't be. It's like a hack, or something which I don't recognize. - One a sheets called Total, I can't use the built in group function and can't collapse the selected section so the project I working on lost functionality. I didn't make any change on that side, but previously it was useable. Thanks for help in advance. Cheers. Zsolt
