I'm trying to calculate total occurrences of a given month. I have included the spreadsheet for reference with totals.
I only want to calculate when the Policy column has a "N". If it has a "Y" I do not need to do anything
If the Policy has a "N" I want the formula to look at the V and P columns and give me a total in the Total Occurrence column. I want it to look at the entire month for a grand total.
If the V or P column has a 4 or less it should equal 0.5, if it has a value is between 4 and 10 it would equal 1. So in the Total Occurrence column should calculate the all of the 0.5 and 1's based on each set of columns and give a total. In the example that I have given in the spreadsheet the total in the Total Occurrence column would be 2 because Jan. 1 has a N and a 8 which equals 1 and Jan. 3 has a N and a 4 which equals 0.5 and Jan. 5 has a N and a 4 which equals 0.5 for a total of 2 occurrences.
I'd like excel to calculate 3 totals for me based on the colour and value on a worksheet.
Basically, I work for various people and they pay at different rates per hour. I currently have a spreadsheet with their names, times, and rates (see attached for example), but I calculate the amounts paid and due manually. If possible I would now like excel to do it.
To explain further, 'J' gives me $10 per hour, and 'V' gives me $5 per hour. Cells shown in red show work done but not paid for. Cells shown in green show work done and paid for.
I'd like excel to automatically create totals as shown on the spreadsheet, namely: Total due: xxxx Total paid: xxxx Total outstanding: xxxx
At any time during the month I can be asked to take on more work - I would then enter the code into the spreadsheet for the hours requested...and I'd like the totals to be update automatically.
I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.
Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)
How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.
I am trying to create a Excel file for my job. I work in a warehouse where I have many different orders all of which are made up of various different numbers of items and priorities. For example:
Order 1 = 45 items, Priority 1 Order 2 = 78 items, Priority 1 Order 3 = 48 items, Priority 2 Order 4 = 34 items, Priority 3
The total number of items is divided by the number of staff to give each staff member a total for the evening.
I plan to have a list of these orders in the excel file.
What I would then like to do is to have each staff member allocated orders so that the their total is reached. The sheet would automatically allocate orders to each person from the list of orders so that based on priority first then by the largest number of items to the smallest.
I think this should be a fairly simple sheet to create and I have a view of creating an application for it if I can get it working.
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:
Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.
Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.
The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.
So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.
I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
I need to calculate the percentage based on self populating score. however I want excel/formula to ignore any cells which has "NA" in it so that it calculates the percentage based only on the cells having Numbers.
Now these Cells which contain Numbers might or might not contain numbers based on the "IF" function which decides wether the cell will have numbers or will remain "blank" or have "NA". To understand better please have a look at the enclosed sample (that would explain my need better)
I have a blank sheet and need to total values in cells to the right of certain information from a range in 90 sheets in other workbooks contained within the same folder.
for example the range is O13:AX500
the first text to search would be "P1", that could be found several times within the range in each workbook with the sheet named "plant"
The value to total would be found in the 2nd cell to its right, ie "P1" in cell O13 then value "£290" in Q13 ::: "P1" in cell X365 then value "£100" in Z365 and so on throughout all 90 workbooks.
The total of the referenced values would then be placed in say B1....
I have around 500 reference codes to total throughout the 90 workbooks so maybe having a list of the text reference down column A may help the process.
Is it possible and can anyone please help with the code to look at a piece of text in new sheet with cell A1 to A500 containing "P1 to P500" and then total all values in the cell 2 to the right in every workbook and place the total in new sheet cell B1 to B500
I want to calculate a running total (so add a new value to its original value). For example, cell A1 is the one I will be changing daily, and cell B2 is the one that I want a running total in. So if I start with A1 being 5, B2 should be 5. If the next day I type in 3 into A1, B2 should now read 8. However I want to do this for a column length, not just cells A1 & B1. So column B would be the running total of the column A values. If A2 is 3 one day and the next it is 6, B2 should on the second day read 9. And on and on down the columns.
I tried doing this without VBA using iterations, but that didn't work. Every time I did that it would add values going down, which was weird cuz none of the cells referenced the cell above it. I didn't know if they would work since I have multiple sheets in this file. (I'm only calculating the running total in one sheet, the others aren't doing anything special.) I need all the sheets in my file and I didn't know if VBA modules are sheet specific/ if you need to be sheet specific when coding.
Another question I have (since I know nothing about VBA) is how do modules work? Do you need to turn them on in the Excel spreadsheet itself? Or when you save it does it just automatically apply itself to the file its attached to?
On another note, how to do this WITHOUT VBA, that would be like 100x more fantastic.
in my worksheet i have different kind of items with its cost. in my case which is not in order, that is, the order of items can be AABAACCBA. I want to calculate Cumulated Total on each row. but i am not sure how to achieve this by conditional formula? the values in my sheet looks like the following,
Date ITEM TYPE AMOUNT Cumulated Total 10-Jan-07 BookA1010 -value(Book) 11-Jan-07PenA515 -value(Book+Pen) 12-Jan-07TableB1515 -value(Table) 13-Jan-07PencilA2035 -value(Book+Pen+Pencil) 14-Jan-07ChairB2540 -value(Table+Chair) 15-Jan-07SofaB3575 : 16-Jan-07RoseC2020 : 17-Jan-07Calc...A3065 : 18-Jan-07JasminC1030 -value(Rose+Jasmin)
find the attachment for reference. How to achieve this using conditional statement or lookups or someother? and i try to avoid macro.
I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.
I am working on this for two days , but I got stuck on the last step. I have a roster for about 35 employees. Calculating the daily hrs was not a problem. But I am doing the roster for one week. And I want employee wise total of hrs worked. I am quite confused as the "sum" formula works for some totals and for others it does not, although all the cells are in the right format. I tried to change the "result" cell to "number" and multiply by 24 to get the hr total as a number, but it does not work.
for example "SUMIF(E1:E57,"rafik",H1:H57)" ( this is the formula for calculating hrs for "rafik" on monday. the result cell is in "hh:mm" format and gives me the right total.
Likewise upto sunday the totals are right. What I want to do is calculate the total number of hrs from mon to sun. This seems to be impossible. the formula =SUM(H60:AL60) in a dd:mm format does not work, even =SUM(H60:AL60)*24 in a "number format" does not work.
I have tried "excel help" , tried to change the format but nothing works. The result should be 52 hrs and I cant get it no matter what I do.
I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.
What I want to do is look down Sheet OEE V20:V500 and get two lots of information -
The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this). The number of occurances of each of the problems it lists in Sheets Reports B1:B100. Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.
I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.
I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.
Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
I need to calculate the total cost of outbound calls based on the total duration of outbound calls multiplied by cost per minute. For example, in a given month, the total duration of outbound calls is 261:16:34 being 216 hours, 16 minutes and 34 seconds. I have this figure in cell A1 with the format [h]:mm:ss. I then convert this to minutes in cell B1 by saying B1=A1, but having the format [m], which gives me 15676. In cell C1, I have the cost per minte value of £0.026. But when I apply the formula D1=B1*C1, I get £0.283, when 15676*£0.026 should in fact be £407.58.
I am trying to come up with a formula that calculates total time someone has worked in a day. The scenario is an individual will work at a home and start working with an individual. Their start/end times look like this in a pivot:
Min Start Max Start Min End Max End Location A+Counselor A 8:56 AM4:01 PM 1:11 PM 7:00 PM Location A+Counselor B 12:00 AM 8:00 PM 6:00 AM 11:59 PM Location B:Counselor C 7:00 AM 12:00 PM 2:00 PM 4:00 PM Location C+Counselor D 8:00 AM 8:00 AM 4:00 PM 4:00 PM
Some people work split shifts while others work a straight shift. The formula I created was this:
=IF(OR(B9=C9,E9=D9,D9=C9),E9-B9,IF(D9>C9,((E9-D9)+(C9-B9)),IF(C9>D9,((D9-B9)+E9-C9),"New Formula Needed")))*24
(I use a pivot table to show max min for start and end times)
This works great except for the individuals that have multiple punches during the same time frame. The one scenario I am having trouble solving for is when someone punches in more than once during their shift displaying. This occurs when a counselor starts a shift working with one person but then adds another person mid shift. An example of this could be:
Location A+Counselor E Min Start Max Start Min End Max End Consumer 1 1:00 PM 1:00 PM 8:30 PM 8:30 PM Total Time: 7.5 Consumer 2 12:00 PM 12:00 PM 2:35 PM 2:35 Pm Total Time: 2.6
Pivot says that they worked a total of 10.1 because it is grabbing the max and mins and calculating. The actual total time worked is 8.5 hours in reality.
The raw data comes in like so:
Location Counselor Consumer Start Time End Time A A A 1:00 PM 8:30 PM A A B 12:00 PM 2:35 PM A A C 12:00 PM 5:00 PM
I need creating a formula that will tell me the total number of employee hours worked during a certain hour (6:00 AM) for a particular department. Some employees clock in at 6:00 AM, some clock in at 6:30. Here is the current formula I'm using which only tells me the amount of employees in the department not how many hours worked . I have three employees that clock in at 6:00AM and one at 6:30AM, the total I'm looking for is 3.5 but my formula gives me the result of 4.
C5:C1446 is a list of departments T1503 is the 6:00 AM D5:D1446 are the Clock In times K5:K1446 are the Clock Out Times
I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.