Calculating A Total, Based On Values In Other Cells
May 6, 2009
Using Excel 2002. Here's my problem.
Column A contains the month (as text)
Column C contains an employee name.
Column O contains a reason for absence.
Column K is the number of hours of absence.
The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence.
E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X.
This will be used on a seperate worksheet where the name C will be referenced from a validation list.
View 9 Replies
ADVERTISEMENT
Jan 23, 2014
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.
View 10 Replies
View Related
Mar 14, 2009
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.
View 7 Replies
View Related
Mar 26, 2014
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.
View 1 Replies
View Related
Feb 2, 2009
I need a way to calculate building rates based selecting the building and the value selected;
I have a list of building construction (fire resistive - protected, non combustible - protected etc)... in cell N8 and the limits in cell O8.
Now what I need to do is calculate the rates in the file attached (cells A5:J21 in my worksheet) based on the building construction and the value/limit I select.
View 6 Replies
View Related
Feb 7, 2014
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.
Table_factors.jpg
View 4 Replies
View Related
Jun 15, 2014
I have a list of houses for sale in various suburbs, divided into houses with various amounts of rooms.
I want to be able to find the average cost of each size house in each individual suburb.
i.e. City Centre, 3 Bedroom, 250000
City Centre, 2 Bedroom, 200000
City Centre, 1 Bedroom, 150000
What is the best way of doing this? Multiple worksheets?
Obviously the sample worksheet I have supplied is quite small, I will be working with much larger lists once.
Sample book.xlsx
View 7 Replies
View Related
Feb 5, 2014
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.
View 1 Replies
View Related
Mar 6, 2008
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.
View 9 Replies
View Related
Dec 15, 2008
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)
View 4 Replies
View Related
Apr 25, 2008
Is there a function, or how would I write a vba to figure out the following.
I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36
View 8 Replies
View Related
Jun 28, 2008
I have multiple sheets cataloging multiple vehicles' mileages in multiple areas (one sheet per month). I want to reorganize this data by Vehicle ID rather than month.
Here is an example spreadsheet to help explain
I have attempted this by writing this formula ...
View 3 Replies
View Related
Aug 21, 2009
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
View 10 Replies
View Related
Jul 15, 2009
I have a row of totals in a spreadsheet and I want to calculate a forecasted total based on the previous month's totals.
For example I have two months and I want to know how to calculate the forecasted Jun 09 total: ...
View 10 Replies
View Related
Jul 3, 2014
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.
View 7 Replies
View Related
Jan 19, 2007
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.
View 4 Replies
View Related
Dec 18, 2009
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.
View 2 Replies
View Related
Feb 17, 2010
Im trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level.
The way the forumla would need to work would be something like this:
(E10) Enter Current Level:
(F10) 37 (which is my current level)
F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11)
Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.
View 4 Replies
View Related
May 2, 2008
I need to calculate time taken to fix a piece of equipment.
A1 B1
BREAK TIME FIX TIME
4/22/08 23:00 4/23/08 04:00
Should be 5 hours, but i can't find the formula to make it work.
View 14 Replies
View Related
Jan 2, 2007
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.
View 9 Replies
View Related
Jun 9, 2014
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.
View 9 Replies
View Related
Jun 10, 2008
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.
View 9 Replies
View Related
Jan 30, 2014
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.
View 14 Replies
View Related
Aug 6, 2009
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.
View 2 Replies
View Related
Oct 7, 2013
I have created a spreadsheet as follows:
Date revenue total tickets sold total tickets sold per day
10-1 166,453 15374
10-2 166,915 15414
How do I formulate so that the numbers automatically update per day
View 2 Replies
View Related
Feb 12, 2014
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
Is this solvable with a formula?
View 1 Replies
View Related
Apr 14, 2009
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
View 10 Replies
View Related
Mar 23, 2013
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
=SUMPRODUCT(--($C$5:$C$1446="Shipping"),($T$1503>=$D$5:$D$1446)*($T$1503
View 6 Replies
View Related
Jun 7, 2014
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.
View 2 Replies
View Related
Apr 16, 2007
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.
View 2 Replies
View Related
