Weighted Average with multiple criteria and looking for specific name
Hi, I'm trying to figure out how to get the weighted average price of multiple products within the same class. For example see the sheet below. I want to find the weighted average price of all coke products, ie Coke/VanillaCoke/CherryCoke. The formula I have tried is: =SUMPRODUCT(--(LEFT(A:A,4)="Coke"),--(LEFT(A:A,4)="Vani"),--(LEFT(A:A,4)="cher"),B:B,C:C)/F2 This doesn't work. Please help!!
Unprotect sheets in a workbook
Hi all I am in need of help, I have a workbook that was protected by another member of staff who has subsequently left. No one knows the password they used and I cant edit elements of the sheets. I have tried the password breaker code as per the web but it just freezes the excel document. I have tried to duplicate and create a new sheet but my excel skills are not great and I cant seem to get the formulas right for the information to transfer any help would be greatly appreciated. KathReplacing the custom list in a sort with VBA
Hello, I want to create a macro to dynamically sort a range to match a manually sorted version of a list. The Question: How do I get a custom list inserted into the below VBA code. Instead of getting an order from the list of custom orders I want to replace whats in quotes with logic to grab something from my worksheet. In this instance it is a list of numbers that has an undefined length so it would be great if the logic stopped adding things to the list once it found an empty cell. ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A10:G10") _ , SortOn:=xlSortOnValues, Order:=xlAscending, _ CustomOrder:="1,4,5,6,3,2,7", _ --I want to replace this list with something dynamic from my sheet. DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A10:G11") .Header = xlGuess .MatchCase = False .Orientation = xlLeftToRight .SortMethod = xlPinYin .Apply End With End Sub Background: There is a list of data points on the first sheet that dynamically turn into column headers on another sheet. I use offsets on the second sheet to turn the list into column headers which is great because those move when I reorder the manual list. However, I make some entries underneath the column headers that need to move around along with the headers when they change order. Right now I have all the rows on the manually sorted numbered and that number is reordered whenever the list is. That number is also pulled over below the column header on the next sheet as an ID. Right below the Column ID row we have another set of IDs for the entries that need to move around to match the headers. When the manual list is reordered the two matching IDs are no longer over each other. The list of IDs from the first table needs to be my sort order for the list of entry ID under the column headers. This will bring the two parts back into alignment after the manual list has been resorted.
Countifs based on data that is cross referrenced with other data
I am trying to count the number of calls I receive to a specific phone number based on the area code from which the call came. I have multiple 800 numbers for my office and therefor I need to know how many calls each 800 number gets based upon the state (area code) of the callers. Below is sample data. I can't seem to get countifs to do the trick. What I am looking to do is have the number of calls that came into each 800 number appear next to that number based upon the area they came in from, allowing me to see how many calls from each state used each 800 number. Below is an example of the desired result. I understand that this may be too complex for Excel, but ANY help would be greatly appreciated. Maybe someone knows a combination of functions I haven't thought of. Thanks.
Excel Formula Help
I at best a novice with Excel so I am reaching out for help. I am trying to calculate hours based of of a letter code for an activity and need the formula to calculate a sum based of of the letter in the cell. Here is a rough way I understand to write it to explain it. I know these are not even close to working formulas for Excel. IF B3=G Then +1 in AC3 IF B3=A Then +1 in AD3 IF B3= R Then +1 in AE3 I have many other cells that will have the letters A,R, or G in them and I am trying to calculate how many of each of these letters there are throughout the sheet into another cell. i.e. total number of "G" in cells B3,K3. But those letters could change to A or R in those same cells and I want the new letter calculated in a different cell. Attached is what I am working on. Any help would be greatly appreciated. Thanks
