I have a question maybe this isn't even possible or it is i'm not that high on excel calculations, but I would like to know this.
I have this document:
[url]
I would like to calculate that if i've rated a 5 (cell C) I would like excel to calculate the time (cell B), the thing is that I want to calculate how much time I've wasted watching movies in every rating 1-10.
Looking for days and I have tried all kinds of things but nothing works. The lates was the Kickbutt find function. Let me explain what I am tring to do. I have an estimate work book with 11 diffrent sheets of material. When I create an estimate I go thru the sheets and select the quanitity of each material needed. What I would like to do is search thru all the sheets and find all the quanititys in column A greater than 0, the copy the complete row A to F to a sheet named FoxOrd.
Trying AronBloods "Kickbutt find function" seemed to be the code to use but I could not get x = " >0 " to work. So I got rid of all the default 0 in the qty column and just left it blank and changed the code to x = "*" which searched and found all the numbered rows but this included the header row when pasted to the order sheet
Code:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
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 am having a problem with some of the cells in a spreadsheet not calculating.
For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).
I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it.
Is there a way that I can speed this process up, using some code when I run certain sections?
There is a copy of what I am doing, so you can see the amount of formula.
I need to find the number of cells containing a specific word. Here's an example:
Site # Visit
1001 12-Aug-2008
1001 LATE
1002 08-Jul-2008
1003 09-Aug-2008
1004 LATE
1005 12-Aug-2008
1005 LATE
I need a formula that will calculate how many "LATE" cells there are.
I work with Excel the more I realize that I'm not that good at it Anyways, I'm in need of a quite advanced formula (for me) that sums up relative relative rows of a specified column. The rows relation depends on the ID-number of the person. Look at my attachment.
In Sheet1 I have an extract of the data I'm working with. It's a medical record of different people in a study. The ID (col. B) seperate the different people. One person can have several perscriptions (spelling?) of medicine. Every perscription has an "fddd", a daily dose (col. L). I want to add up all the daily doses for every seperate ID of my data in Sheet2. So for example, for ID1 I want it to sum L3:L6 in Sheet1 and return it to Sheet2!B2. If the ID is not found I want it to be blank.
See the attatched spreadsheet. I can not seem to figure out a formula that will calculate the totals for each type of data.
My goal is to have a sum for each location for # of audits, Critical Counts, and Major Counts.
At the moment I have a simple formula that calculates the due date of a task
The log in date is in cell B3, I use a simple formula in cell F3 "=B3+20" - This gives me the due date
I now need to add another log in date but use the same due date cell
So now ....
Either B3 or D3 will contain a date - whichever one is populated I will need the due date displayed in cell F3
If I enter anything in a cell on one work book I get calculating cell for about 3-5 seconds.
I don't have any formulas looking at the book. and I have deleted, renames, copy and pasted, exported to text and reimported and still it does it.
The thing that is odd though is that if I press return again it clears the calulation cells message.
I am calculating ratios to show as 1:0 or 3:2 etc using this formula: =IFERROR(CONCATENATE((K2/GCD(K2,N2)),":",((N2/GCD(K2,N2)))),"Nil")... I have a lot of columns that have zeros in them and the formula i am using doesn't seem to work for those...
Example
A B
3 6 should equal: 1:2 works OK
2 0 should equal: 2:0 my current result: 1:0
3 0 should equal: 3:0 my current result: 1:0
0 1 should equal: 0:1 works OK
0 0 should equal: Nil works OK
4 1 should equal: 4:1 works OK
Anything with a zero in the first column comes up as 1:0
Formula "(Cell-Cell)/ABS(Cell)"
When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me
I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.
Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number
PercDiffExample.xlsx
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 am trying to calculate an average of 800 cells, where I would like to exclude a cell when a certain character is in it, namely "<".
View 4 Replies View RelatedI have the following on a spreadsheet that keeps track of when student work is handed in and calculates how many days late it is so a penalty mark can be given.
A1= User entered value : a fixed submission date that a piece of work is due to be handed in on.
A2 = User entered value : The date entered if work is handed in on time or early
A3 = User entered value : The date the work was handed in if late
A4 = User entered value : The date the piece of work is due to be handed in if an extension has been granted
A5 = Calculated value : How many days late a piece of work is
Essentially I want the calculation in A5 to reflect how many days late a piece of work is. SUM(A3-A1) would do this of course. If the work is early or on time, A2 is ignored.
Sometimes however students have permission to hand work in late, in which case another calculation is needed to work out how many (if any) days over their extension date (A4) their work is. How do I combine these two calculations into the same cell, so that if A3 is empty it checks A4 to see if the student has an extension date and then returns a 'days late' value if applicable?
I am using Arrays throughout a large report and each time I move around in the report or simply open the report I have to wait 5 to 10 seconds to wait for the cells to calculate. Is there a way to prevent this from happening other than changing the calculation option to manual.
Is this and array problem? I don't get this when I use multiple vlookups and choose formulas? Any ideas to speed up or prevent all together?
I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.
This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "N:N"
Dim Cmnt
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Row > 3 Then
If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6
End If.....................................
I have what is probably a simple problem for most, but can't figure out what to do.
In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
I am working in Excel 2003. I consider myself a little more experienced than a beginner, but not quite an intermediate. My problem is, I am doing percentage changes comparing the average year-to-date data of 2009 through 2012, and then I am attempting to get the percentage change of the current year-to-date data to the average of the aforementioned years.
I took an average of these numbers, that are pulled from other worksheets. The cells are set as "General".
112241 = 1.833333333 or rounded, 2 divided by 1 (the current year to date total), the result is -45%, which should be -50% decrease. The percent cell is set at percentage.
It appears the cells are not automatically rounding up.
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 get a problem in preparing a roster i went to ignore blank cells in calculating rank. and also i went to make the sum to be empty is one of the cells in a range is zero
View 1 Replies View RelatedUsing 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.
im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100-100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver.
View 9 Replies View RelatedI have some activities that needs to be done ina specified time
Like for example
TAT HRShrsACT 14ACT 28ACT 324ACT 42ACT 54
And i have a sheet where in i need to calculate turn around time
ActivityStart timeEnd timeTATACT 1ACT 2ACT 3ACT 4ACT 5
Based on time entered in start time and end time the TAT column should calculate the TAT based on the above standards.
I also would liuke to exclude weekends(Saturday, sunday). Also the TAT should only be calculated during working hrs between 7:30 PM to 4:30 AM.
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.
Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.
To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.
I am on an auto show board and want to come up with a way of calculating the ballots that come in, using excel. Ballot points are accrued as follows: 5 points for 1st place, 3 points for 2nd place, 1 point for 3rd place. With about 25 different categories (classes) and I am estimating participants at around 100.where I can in the automotive field knowing there is nothing besides personal gain.
For my Intro to Engineering class, I collected data of two filter media in the laboratory. The data included the % of the media which passed through a sieve and the sieve openings. For use in a certain equation, I need the opening size at the 10th, 30th, 50th, 70th and 90th percentiles. I've tried searching on the Microsoft website but I don't think the PERCENTILE function will work correctly with the given data. I also tried using the slope between points to find the opening sizes at the percentiles mentioned, but doing so yielded different results from my professor's. Attached is the data I collected organized in tables and graphs of the data. I have also attached a pdf file of an example which my professor has given us.
View 5 Replies View RelatedI think a really easy no brianer, I need to find the difference in percentage terms of two values. I am using =(B2-A2)/ABS(A2)
where B2 = 24.09
and A2 = 21.08