I am thinking that I must have something set up wrong some place, but have not done anything that should have changed my formulas
Its not that they are gone but just are not auto calculating.
I tried just a basic sample invoice and the figures do not calculate without manual calclation
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies View RelatedI've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.
At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.
EXAMPLE:
TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00
Employees - 4
SUM - £4,237.86
Median - £1,033.93............................
To start, have 1 sheet with approx 25 columns of data and i need one column to check 3 others + another sheet to complete its fields.
In column M i need to enter some prices from another worksheet (tag name 'prices') but these prices depend on whats completed in the fields in columns L, T & V.
Column L has fields completed with eg. Houses, Offices, Warehouses
Column T has some fields completed with eg. B1, D, E (these are just zonal refs)
Column V has some fields completed with numbers 1,2,3
Prices worksheet has 4 columns
A = Property (same heading and fields completed as Column L on first worksheet)
B = Standard Price (this has prices in fields that correspond to the properties in A above)
C = Cresta Price (same description as above)
D = Wind Price (same description as above)
I need column M (blank fields) to look at L (heading 'property') then add in Standard Price from B (worksheet prices) then check to see if any fields are completed in Columns T & V and if they are then it needs to addon the prices from either columns C or D from Prices worksheet.
I am a little lost on this, and have tried to remedy my issue by searches to no avail. I have a cell formula that references another cell:
Cell A1 (=A3) where Cell A3 = 1
Cell A1 displays the text "=A3" and not the value of 1. Autocalc is on, and the worsheet and workbook is unprotected.
I am having trouble with formula to calculate daily results.I can only calculate results base on daily plan.
please follow this link [URL] .....
Part A
2/12
3/12
4/12
5/12
6/12
plan
0
0
50
50
0
[Code] .....
I need to calculate the amount of time my daughter will be in daycare which varies each week. I have to give the same sheet that I have hidden calculations on to the center. I have to have the time she arrives at 7:15 AM (Cell A1) however they don't start to bill until 7:45 AM (Cell A2) and continues until 11:30 AM (Cell B1) and need C1 to be total hours between A2 and B1
I need a formula that will calculate the hours between 7:45 AM to 11:30 ONLY if cell A1 has a time entered in it. If A1 is blank then it should calculate 0 hours.
Example (WITH something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - 7:15 am to B1 - 11:30 am C1 - 3.75 Hours
Start Billable Time
A2 7:45 am
Example (WITHOUT something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - to B1 - 11:30 am C1 - 0.00 Hours
Start Billable Time
A2 7:45 am
If it has to be in 24 hour time is there a formula that I can enter in another cell to convert 12 hour time to 24 hour time again ONLY if A1 has something entered in it?
I am using =COUNTIF formulas to calculate different conditions. such as, calculating how much of one product do we have and how much is is received under 30 days. i want to combine those formulas in one cell to calculate two conditions in different columns.
1-So first i found out the date difference. I used =DATEDIF(Sheet1!C10,Sheet1!E10,"d")
2-Then I found out how many of one type do we carry, using the =COUNTIF.
=COUNTIF(Sheet1!J2:J3810,"DAZ")
3-That was easy, But now i have to sepereate them into 30 or less, 60 days or less, 90 days or less.
Then i used =COUNTIF(D2:D3810,"
I would like to ask the formulas for this... for example:
$35 .. discount 10% + 25%
instead of having so many cells to calculate $40 x (100%-10%) = $36
$36 x (100% - 25%) = $27
I am trying to do a spreadsheet that calculates the monthly rent from 9/1/14 through 1/1/15, then rent gets reduced from 2/15/14 through 1/1/16, then after that there will be an increase of 3% per year for each year until 8/2019.
View 1 Replies View RelatedI got a workbook that is linked to many external workbooks and it is very slow to calculate, so I work it with manual calculation and only calculate the sheet (SHIFT+F9) when necessary. To work with this workbook I need to be able to make faster manual calculations. I have tried many solutions and the only one that seems to work, is to work without the links to the other files (by breaking the links and turning the formulas to values). The problem with this solution is that I can't lose the formulas. I think that must be a way to calculate the sheet without calculating the cells that got a reference to the external files. Only the formulas in the current sheet.
View 3 Replies View RelatedIm having an awful time with the time it takes to calculate my formulas. I have probably written some of them ineffiecent.
=INDEX( Website_Query!$A$2:$Z$10000,MATCH(1,(Website_Query!$A$2:$A$9725=B1144)*(Website_Query!$C$2:$C$9725="EA "),0),4)
=Q1144*2
=IF(ISERROR(VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE)),"",VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE))
=IF(ISERROR(Q1144+AR1144),"",Q1144+AR1144)
=IF(AK1086="q2",AL1086+AS1086,IF(AM1086="q2",AN1086+AS1086,IF(AO1086="q2",AP1086+AS1086,IF(AQ1086="q2",AR1086+AS1086,""))))
="q"&INDEX(Website_Query!$A$2:$L$7725,MATCH(1,(Website_Query!$A$2:$A$7725=$B1086)*(Website_Query!$C$2:$C$7725=$C1086),0),5)
I am looking for ideas on how to speed-up one workbook. I have a spreadsheet with hundreds of lookup formulas and array formulas which are very slow to recalculate (takes around 5 min). Good thing about it is that these formulas are located in a specific range and that I need to run them only once a day (in the morning). Since I need to refresh the rest of the spreadsheet frequently, I am looking for an idea on how to isolate this resource heavy areas.
One solution I've been thinking about is moving these complex and slow formulas to VBA and run them only when needed. Can anybody assist me with the best and easiest way of doing this. Ideally I would want to leave option to the user to later edit this formula. Does anybody have any experience with weather it is best to migrate complete formulas to VBA or maybe leave formulas in the spreadsheet and run them based on predefined flag (that I can switch on or off from the code)?
I have the same question as described in Graphs Not Updating When Source Data Updated; however, the poster did not reply to the request-for-clarification questions.
My chart does not always update when my source data changes. My Calculations setting is Automatic (not Manual). There is no macro code working to produce the source data or the chart. My chart is on the same worksheet as my source data. The source data is simple links to other cells in the same workbook that update as the underlying data is updated. Saving doesn't work. Manually calculating (F9) doesn't work. The only way I have found to get the correct data to display in the charts is to open the Source Data and re-enter the Series range. Obviously, that gets old.
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
I am working on word documents that has financial numbers on them. I am copying word pages to excel to calculate the numbers by using formulas and etc. I will cut to the chase; is there a way or a macro to insert into an excel template workbook(.xltx) when we open the template there will be a command button to select the word document (which we want to copy its pages) to excel sheets. When a new page begins a new sheet will be created and the page will be pasted on the new sheet.
It is not important if it has to have a command button to select the word document it is ok to run the macro and select the word document and it does not has to populate sheets according to length of word document (I can create lost of free sheets on the template)
The main idea is to gain the time I spend on copying the word document to excel sheets. Some documents can be very long (100 pages). I am using MS Office 2007.
I triend to export data but excel does not allow me to select word documents.
I have a sheet that contains rows containing letter codes for example "CU". This is generated by an IF formula i.e. If true = "CU". However I need to be able to calculate the number of instances of "CU" but sumproduct will not work as it sees the cell as containing a formula not the result i.e. "CU". Sumproduct code:
View 3 Replies View Relatedwhere is possible to get more detailed manual - reference - for using ADO in excel? Help in Excel doesnt content much information of ADO, I have tried to find other manual, but no results.
View 4 Replies View RelatedI'm trying to turn calculation to manual, but there does not seem to be an Options button under tools on the mac I'm using. I've checked another mac and it is also missing. I'll probably end up using a pc for the calculations anyway, but I was wondering if anyone knew what was up?
View 3 Replies View RelatedIs there a way to make excel 2007 pop up a warning whenever calculation is set to manual by a macro or any other means? I have on several occasions noticed formulas not working, only to discover that calculation was set to manual without me noticing. And then I don't know how much of my work may have been afffected. This seems like a pretty vital piece of information, and I am surprised that it's not made more obvious.
View 11 Replies View RelatedI have initiated a spell-check in VBA using:
View 14 Replies View RelatedI am trying to calculate the present value of a terminal period in Excel. The manual calculation and excel PV function are off by about $98,000. Both calculations are using the same capitalization rate and terminal life. My PV Excel formula is as follows: =PV(discount rate-growth rate,remaining term (years),-terminal cash flow,,0)*present value factor in last year of cash flow) What is causing the difference in values? Is there something in the Excel formula that is causing the difference?
View 1 Replies View RelatedI have a macro that transfer data from one sheet to another (I have sheets called Form and Sent). Basically, the users enters data in the Form Sheet. After they're done, the data gets transferred to the Sent Sheet. I don't want users to be able to modify the data in the Sent sheet. I just want them to see the records. Also, i want the sheet protected from having users manually inputting datas. I can't accomplish this when I protect the sheet, since it is giving me an error while running the macro to transfer data. Is there a workaround?
View 9 Replies View RelatedI have what I'd thought would be a simple problem, but I haven't yet been able to track down an answer. I am trying to manually rank a list (column) of players' names. What I'd like to be accomplish is something to the effect of being able to enter a value for a single player, then have the cells resort themselves AND update the list.
Example:
RANK PLAYER NAME
1 Jon
2 Jim
3 Joe
4 Jack
5 Jane
I'd like to be able to manually change A5, for example, to 2 and then have the list update itself to read:
RANK PLAYER NAME
1 Jon
2 Jack
3 Jim
4 Joe
5 Jane
I've toyed with macros that took care of the sorting piece but I'm still left with duplicate numbers (ranks) that I must then manually change and/or fill down. There are over 500 entries (rows) so this can become a bit tiresome.
how does excel determine if it opens a file in manual or automatic?
how can i choose that excel opens every file in calculation manual?
Screenshot (72).jpg
This is probably a really stupid question, but I can't for the life of me figure it out. I need to do some very basic filtering, but the dropdown box where you check off the things that you want to filter by is not defaulting to show checkboxes. This didn't seem like a big deal at first, but it's made it impossible to filter all but one things. For instance, to filter everything but values that are 0, I would have to manually click every single value in the dropdown box. Clicking "select all" is doing literally nothing. It's this way for all my excel documents. how to get the boxes back? I've included a screenshot of what comes up whenever I click on the manual filter button to show what is coming up.
I am on a Mac running OS 10.4.11. Whenever I launch Excel 2008, I have to go to Preferences and set Calculation to automatic.
Then Excel calculates automatically until the next time I launch the program. Then I find it has defaulted back to manual.
I have an action that deletes a row, and decrements rows counter by one. But, if the user manually deletes one row, I don't get that information (decrementation). Workbook pretection is out of the option.
What are my choices?
I was thinking, is it possible to capture a delete event in general, and add some code to it? Or maybe even completely disable it?
I have a pivot table and if i select my headers i have the availability to sort a-z and z-a plus manual ...manual says to drag the item where i would like it... I have tried the excel database..they reference manual sort but really give no explanation.
Scenario.. I want to sort a-z.. then manual sort a few items to the top of the list..
