Formulas to highlight cells
Hello! Thank you so much in advance for your help! I have a cell in A1 that contains: Composer A (BMI) 50% [292650159], Composer B (ASCAP) 30% [154541151], Composer C (SESAC) 10% [55547898], Composer D (SESAC) 5% [4125475], Composer E (SOCAN) 5% [02124054] 1) I'd need a formula to use in Conditional Formatting that will turn this cell green if the sum of the % equals 100%. 2) I'd need another formula to turn the cell red if the sum doesn't equal 100%, and no highlighting if the cell is empty. *** I have another cell in B1 that contains: Publisher A (BMI) 50% [612559885], Publisher B (ASCAP) 35% [02135474], Publisher C (SESAC) 15% [01023545] I'd need a formula to highlight the cell in green if the percentages for BMI and SESAC match the ones in the previous cell (A1) for composers and if the sum of all the % in this publisher cell equals 100% (SOCAN composers go under the ASCAP publisher in this example that's why there is no Publisher for SOCAN). The cell should be red if there is an issue with the % splits. I really appreciate your help! Thanks again!
Excel Formulas and Functions
I'm creating a spreadsheet to help keep logs of jobs I do daily and weekly. I need a formula to add rows in a column, then subtract a certain amount as long as the amount is over a certain amount.. Example; I have 4 or 5 rows that the amounts are over $51.00 I need to add those rows and then subtract $14.00 from each unless the amount is under $51.00 then I need to subtract $6.00
Need help with concatenate formula
I've managed to put together a spreadsheet because I'm trying to reduce time it takes to do manual data entry. I've tried some VBA but my skills are no where near what they should be to use it. My formula skills are okay, but I learned it on my own so my knowledge is limited at best. To keep it brief, I work for a newspaper as a data entry clerk in the sports department. We have to enter schedules in their system Adobe InCopy. Obtaining the schedule data requires manual lookup on specific high school athletic websites. Example: I manually go to high school A website athletic schedule and find out who they are playing a certain day. I then go into the company system and manually type team A at team B, 4:30 p.m. ... Yes, typing the periods in between pm is quite tedious. In the spreadsheet I put together, I managed to allow typing a number that represents the school so instead of type out the word "team A" I type "1" and that specific team enters into a specific cell. Likewise for the team they are playing and the comma and time. The time is also represented by a certain number; i.e. 3=3:30 p.m. After typing the numbers in that represent the words, I created another cell that includes those words all concatenated together in one cell. Ergo, once I'm done typing the numbers, the sentence of "team A at team B, 3:30 p.m." is ready to be copy and pasted into the company system. It has shaved off approximately 20 to 40 seconds of data entry saving a ton of time. Now what I'm trying to do is hit up a different part of entering these schedules. Sometimes these schools have a tournament amongst multiple schools. When this happens the data entry appears as such: "Lincoln High School tournament (team A, team C, team F), 2 p.m. The data in the parenthesis can vary from 1 school to as much as 14 schools. I managed to create something similar to the above method of entering a number and then the data generates in a concatenated cell. When I did this, I used rows H25 all the way down to H37, allowing the user to enter the number of the school and then having it generate over to the concatenated cell. The problem I'm having, is that (as you will notice in my formula) when I delete a number that represents a school, the field where the concatenated data comes from turns to #N/A, thus completely removing the entire concatenated cell to an error showing the #N/A in the cell where the concatenated data should be. I've gotten as far as using IFNA and ISNA along with other formulas like TEXTJOIN and ISBLANK and the furthest I got where I'm pretty much stuck is IFNA and/or ISNA don't fully read the cell range of H25 to H37. The data that appears in the concatenated field only reads two schools (1 of which is randomly from the middle of the list). If someone can help me here that would be great. I'm trying to get it so that the list from H25 to H37 is variable to the user and they can type in whatever schools they need to and for the concatenated cell to ignore blanks when there isn't anything entered in, for example cells H30 to H37. In my example only cells H25 to H29 would be filled with a number representing a school and the format I explained earlier in the concatenated cell would appear like so: "Lincoln high school tournament (team A, team C, team F, team G), 2 p.m." Here are the formula's I'm trying to use, however if anyone knows of a better one or more efficient please let me know: Original one I created (it works but I can't delete anything out of cells H25 to H37): =CONCATENATE(E21," ","(",G21,","," ",H25,","," ",H26,","," ",H27,","," ",H28,","," ",H29,","," ",H30,","," ",H31,","," ",H32,","," ",H33,","," ",H34,","," ",H35,","," ",H36,","," ",H37,")",",",H21) 2nd one: This was the closest I came to making it work with IFNA, but it doesn't read all the schools in H25 to H37: =CONCATENATE(E21," ","(",G21,","," ",(IFNA(H25&",",""&" "&(IFNA(H26&",",""&" "&(IFNA(H27&",",""&" "&(IFNA(H28&",",""&" "&(IFNA(H29&",",""&" "&(IFNA(H30&",",""&" "&(IFNA(H31&",",""&" "&(IFNA(H32&",",""&" "&(IFNA(H33&",",""&" "&(IFNA(H34&",",""&" "&(IFNA(H35&",",""&" "&(IFNA(H36&",",""&" "&(IFNA(H37&")",""))))))))))))))))))))))))))) Thank you
VBA code: Count number of times a cell is changed if cell = Yes
I'm currently using a VBA code that tells me in Cell B15 the current number of times Cell C15 has changed, and that's great, but I have other cells (Cells F16 through F100) that I'd like to have start at a base of 1 and add 1 to the base value each time the specific cell changes if the other cells are activated via the keyword "Yes" in Column D (Cells D16 through D100). Not all of the cells are going to be activated at the same time and I'd like to be able to reset the number if at all possible to have it scale up more than once. Here is the formula I started with that is keeping track of the first changed cell. Private Sub Worksheet_Change(ByVal Target As Range) Dim xRg As Range, xCell As Range On Error Resume Next If Target = Range("C15") Then xCount = xCount + 1 Range("B15").Value = xCount End If Application.EnableEvents = False Set xRg = Application.Intersect(Target.Dependents, Me.Range("B9")) If Not xRg Is Nothing Then xCount = xCount + 1 Range("B14").Value = xCount End If Application.EnableEvents = True End SubFormula Problem Countif based on Unique value and based on another comlum
I will first off say I am a travel agent to give you some context on what I use this information for. I have basic knowledge in Excel and it has all been self taught. For this spreadsheet my Microsoft Flow will input data into this spreadsheet and the counter at the top will keep track of how many seats/rooms I have sold vs unsold for group bookings. *For the number of people I think I have it figured out. The only correction I would try and make is counting adult seats. My way works but I dont think this is the best way. Ideally I would like to count adults it counting the number of people that do not have a value in the Child's Age Column. If that cant be fixed then that's fine. What I am using right now is just Minus-ing the amount of teens/kids/infants. ds/infants Now Onto my bigger issue and the main reason why I am posting. Counting the number rooms is more difficult. Each room is generated an ID through Microsoft Forms/Flow. This is their "room number". 1. For L5 I would like it to be total of Junior Suite Rooms. From what I am thinking it has to count the unique values (room number) from a14:114 and it has to match it with what rooms (k14:k114) that are labeled as Junior suites. I believe I have use a countif or sumif but all the different ways wont work for me. This would = the amount of Junior Suites I have sold (Keep in mind I dont just want to count all the Junior Suites(K14:k114) since sometimes there is anywhere from 1-4 people in a room) 2. Now After that I want to calculate L6, L7, L8, L9,L10, etc. What I want it to do is calculate the unique values from Room Number (a14:a114)+match the rooms labeled as Junior Suites (K14:k114)+ match it with the Departure city (H14:114)(Regina, Saskatoon, etc.) This would then = the amount of Junior Suites I have sold from Regina (or Saskatoon, etc) Total Number of rooms is a number I put in (no formulas). Then once I have the above two situations figured out "unsold" as a simple formula. For example "total"-"sold"= unsold. This is something I know how to do. Thank you in advance!! Between this and Microsoft Flow this is going to save me so much time and I am super excited! Thank you, SamanthaFormula Issues
What is the formula to divide words in a column to reach a percentage? For example, if the words are "One""Two""Three" I'm looking to divide the total amount of "One"and"Two" by the total amount of "Three" and "One". So if there were 1 "One", 1 "Two", and 16 "Three" the equation would be 2/17 and the answer would be 11.76%.Conditional Formatting across different sheets
Hi everyone, I would greatly appreciate some help. I am trying to set up a rule across two different sheets within the same workbook. On the first sheet which is a summary sheet for all the sheets in the workbook, I have cells containing dates as to when a questionnaire was completed. On the second sheet, which goes more into the detail, I have a 4 cells - one: containing the date it was originally done, two: the date it is due to be updated, three: a formula turning the cell red when the due date cell (two) is out of date, and four: a cell with a 'number of days' formula ( I hope this is all understandable). What I am trying and hope I can do is: - On Sheet 1, turn the cell with the original date red WHEN on Sheet 2, that date's corresponding red due cell (cell three) has automatically highlighted. If this is not possible and you have an idea of how I should do the formatting instead, I welcome all insight and ideas. Thank you!
Help with a Formula please
Hi, Here is how the sheet looks so far Dip Sampling Rejected Dip Sample Cleansed Dip Sample Total Success Rate To Success Rate Target 1 4 5 80.00% 96% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 So my problem is I am trying to work out how many more cleansed items I need to hit my target of 96% in Success Rate. The cleansed and rejected items automatically increase the Dip sample total and the Success Rate amends to reflect this throughout the day. I need it to tell me based on the info populated how many more cleansed I would need to turn the current success rate into my target % in the to success rate field. My target can be raised or lowered depending on the project so need it to use whatever my target is set as in that box. I would also like it to return Target Met when the target % has been hit or gone above(this is not essential though. I have added a test sheet please anyone if you can help this would be amazing as I am pulling my hair out and need this for work asap.
