Formula's Keep Disappearing Formula Does The Calculation And Then Disappears
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...
however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!
one such formula is:
=IF(AND(AC11"No Calc",AC11>=1,AC11
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macrocreated Formula Disappears Without Pattern
On a sheet, I have a Worksheet_change sub that checks if the cell changed is in column 1, 10, or 19 and if it is, then the six cells to its right are filled with formulas I need. All formulas work, except for one. I've checked over and over again and the formula itself works fine  the coding behind it is exactly what it's supposed to be. But for some reason sometimes the formula isn't put into the cell. I haven't found any pattern yet with this problem. Even stranger is that the formula is identical to one in another column before (which is also entered by the macro) and the other one has never disappeared yet. I'm setting both formulas with the abc.FormulaR1C1 property (I don't use the abc.Formula property to avoid having to write three different formulas  one for each possible column change).
View Replies!
View Related
Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating. I've got a formula =IF(COUNTA(A1)=1,TODAY(),"") When something is typed in Cell A1 the cell with the formula will input todays date. Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View Replies!
View Related
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass. To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them. Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View Replies!
View Related
Calculation Of Time Formula
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details.... The formula currently being used is... =IF(G50=E50, H50F50+(H50
View Replies!
View Related
Date Calculation Formula
is a formula to work out how many paid sick days an employee is entitled to based on their employment start date Up to 6 months service the entitlement is 5 days After 6 months it is 20 After 1 year 40 After 2 years 60 After 3 years 80
View Replies!
View Related
Formula: Choose The Calculation
I am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell
View Replies!
View Related
Step Calculation Formula
I have a simple calculation  Say 06. What i need to do is, if the value is <7 place a 10 in the cell, or if the the value is <12 place a 5 in the cell, or, lastly if the value is <21.5 place a 0 in the cell. Is this at all possible  as i can only do it with conditional formatting using colours at present
View Replies!
View Related
Need Assistance With Average Calculation Formula
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula. =IF(K23="","",IF(AND(K23=E23,ABS(B23AVERAGE(F23:J23))>B20),B23ABS(AVERAGE(F23:J23)B20), IF(AND(K23=F23,ABS(B23AVERAGE(E23,G23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,G23,H23,I23,J23)B20), IF(AND(K23=G23,ABS(B23AVERAGE(E23,F23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,H23,I23,J23)B20), IF(AND(K23=H23,ABS(B23AVERAGE(E23,F23,G23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,I23,J23)B20), IF(AND(K23=I23,ABS(B23AVERAGE(E23,F23,G23,H23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,H23,J23)B20), IF(AND(K23=J23,ABS(B23AVERAGE(E23:I23))>B20),B23ABS(AVERAGE(E23:I23)B20),0))))))))
View Replies!
View Related
Formula/Function For Commission Calculation
I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for.. $0  $10,000  60% commission $10,001  $15,000  65% commission $15,001+  70% commission.. i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated.. ps.. my excel sheet is set up as follows: Rows ag (stuff that is irrelivant) row h, gross fees row i, commission (in dollars)
View Replies!
View Related
No Calculation Flag, And Percent Formula
1. In neighborhoods that have zero units in a given price range I have it to display "" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "". How do I get excel to glance over "" and flag it for no calculation? 2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly. For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision. e.g. Grand Peninsula=B5/N3 Meadow Glen(Mansfield)=B6/N3 ...etc Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
View Replies!
View Related
Declining Balance Calculation Formula
I need to locate/write a formula that can calculate the declining balance on revolving interest loans, such as a credit card. The formula needs to calculate the number of remaining monthly payments based on a stated interest rate and payment amount (i.e., present balance $2000, annual interest rate 18.99%, payment $60 per month). And then, if possible, the formula also needs to translate the answer into a definite Month and Year going forward from today's date (or calculated in another cell of the worksheet for display). In essence the formula would begin as (2000 + (2000 * (.1899/365)) * 30)  60 = then repeat the calculation using the answer above, and so on, until the original principal amount had declined to $0, and finally count the number of months it do to get there. This is essentially a mortgage type of calculation. I know I could write the formula repeatdly one month at a time across a few hundred/thousand cells, but there must be a more economical way to do so.
View Replies!
View Related
Formula Working On A Spreadsheet To Control The 'booking Diary'
I've been working on a spreadsheet to control the 'booking diary' at work and linking it with the movements in and out of our 'chambers' If you look at Column L & M, they are the volumes and column N is where the 'pallet' is coming / going to . If you look at line 149 and below I've tried to translate these 'volumes' into movements by time but as you can see it is not to totalled up correctly during certain times. For example line 158 should total 40 and line 187 should total 80. Can anyone work it out for me as this happens on numerous lines
View Replies!
View Related
Formula Making A 'sports Day' Spreadsheet For A Project
I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum. I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th. [url] basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.
View Replies!
View Related
Rank Calculation (with Correction) In Array Formula
I'm trying to calculate the sum of rank vlaues in an array formula (required for a MannWhitney Utest calc). For example, I have the results of a survey quesiton (15 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties. The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below. $C$% = "Sales" and $F$5 = "Marketing"... ={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}
View Replies!
View Related
'greater Than >' Formula & Result
Column B is basically 550 points + column C = Base Roll. We need to introduce a cap so no more than 1000 points can be displayed/earned. So column G adds all points & column F caps. So far it's all ok. Now when i add a value to column E (points spent) those points are deducted from column G instead of the capped 1000. Entering a value of 500 displays 800 in column F instead of 500.
View Replies!
View Related
If Statement (put 'IF Formula' In Cells)
I am trying to put an "If statement formula" within cells using VBA but it errors at the word "error". Basically what I am trying to do is put similar to the following example into a range of cells.. for example: =IF(LEN(Verbatim_LU!B2)>100,"ERROR","OK") ...with B2 being worked out by the for..with and the value (in this instance 100) being put in by VerbLen. NB: Column A contains a reference number hence not having this actioned on it and counts start from 2
View Replies!
View Related
Formula For A # In A Column Of #'s That's Closest To A Certain Val
I'm looking for a function or formula in Excel that will find a number in a range of numbers that is closest to a specified number. Example: I have a column of 7 cells that contain cummulative percentages from 0  100. It will always be theses same cells, but will have varying percent values depending on other calculations. I need to find the first number that's closest to and below 10. I also need to find the first number that's closest to and above 10. I need this for 3 other values (40, 50, & 90)as well, but if I can get an example of a formula, I could probably go from there.
View Replies!
View Related
If Formula: Identify New Products That Are Not In My 'stocklist'
I am trying to identify new products that are not in my 'stocklist'. In the 'new prices' sheet I have =IF(ISNUMBER(MATCH(C4,'Stock List'!C:C,0)),"","NEW") dragged down in column G. Though its throwing out 'New' on every item which is incorrect as there are loads of items already in both sheets. This sounds confusing but if you look at the attached im sure you will see what im trying to do. One last problem is that some of the new codes have zeros in front of them so the formula will have to discount the 1st couple of digits IF they happen to be zero. example; C5480 in stock list is exactly the same as C398 in new prices. But because there is a zero in front of the C398 then the formula does not recognise that the two are the same.
View Replies!
View Related
Formula To Count 12 Months Back For Finance Calculation
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row. We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months. For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets. In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month. Here is the formula: =(FVSCHEDULE(1,M79:M90/100)1)*100
View Replies!
View Related
Formula Structure: The Total Fees (H2) Is A Part Of The Calculation
I am attempting to calculate commission (J2) based on the data entered in cell D2 1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%. 2. If the Loan Description is specifically 80/20 then 80% of the Loan amount is used in calculating the commission. (note: 80/20* is also a valid entry). Otherwise the total loan amount is used. D E F G H I J Loan Dsc. Int. DSCNT% YSP% Fees Loan Amount Commission 80/20 7.52.0 1.0 3.0 $137,403 $4,122.09 I attempted this formula and obviously its incorrect: Calculating Commission (J2) =IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01)) 1. If H2 is blank then TRUE enter a blank 2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.
View Replies!
View Related
Formula Not Updating: Using Tools, Options, Calculation, Automatic
I have a table with rows that keep growing. But I have place formula in the whole of column F, i.e. F2:F66565. When I import information from MS Access into column A to E, the formula in F does not work, until I copy from F2 to the end manually. I have tried using Tools, Options, Calculation, Automatic. That doesn't work, I have also tried F9, that doesn't work and I have also tried checking Precision as Displayed under the calculation tax in ToolsOptions, that doesn't work either.
View Replies!
View Related
Lookup With 'sheetname' In Formula
I am collecting a lot of prices from over 60 countries. Each country has a seperate sheet in a workbook, named "Country" Spec (eg Australia Spec, China Spec etc.). These sheets are all setup exactly the same. I have the following sheet wherin I want to show the data coming from all the different sheets:  a  b  1  Australia Spec  "Formula"  2  Bangladesh Spec  "Formula"  3  China Spec  "Formula" In B1, 2 3 etc I want to do a lookup of a fixed cell on the correct country sheet. Therefore I want to include the sheetname in the formula. Thus I would like in B1 the content of cell q13 of sheet Australia spec. In B2 the content of cell q13 of sheet Bangladesh Spec. My current formula is: =INDIRECT(a1&"!$Q$13") But it doesn't work
View Replies!
View Related
Multiple 'if' Scenarios In A Formula
What I am trying to do is a formula for two different 'if' scenarios that will return one value for one answer and another value for the other. They are not numbers, they are text. For example: if the cell contains 'pcs' value would = no if the cell contains a dollar amt value would = yes
View Replies!
View Related
'Post Formula' Button
I may have missed it, but I can not seem to find where I can customize Excel to add a "paste formula" button. (I have a fair amount of copying to do where I do not want the formats being changed at the same time.)
View Replies!
View Related
How To Create A 'dynamic' Formula
I have a large spreadsheet file with numerous worksheets. Each sheet represents one month of the year and is named Jan05, Feb05, Mar05 etc. In the "Summary" sheet I want to automatically reference a range in the other sheets. For example: In the Summary sheet column A, I have the sheet names: Jan05 Feb05 Mar05 etc. In column B, (let's use B5 as an example) I want a formula that reads something like: =B4 + Apr05!C7 This seems simple enough, but how can I write a formula that 'automatically' enters the sheet name (i.e. "Apr05") in the formula? The row containing the data for Apr05 is only created when that month comes along, in order to keep the file size as small as possible at any time.
View Replies!
View Related
Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result
I'm quite a novice at Excel. I have a column of values that I sum as follows; A 0 0 0 0 0 0 <sum of A1:A5 A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
View Replies!
View Related
Using A 'what If' With A 'sum' Formula
I am trying to create a formula where it will add cells together from mulitple tabs within excel but it also needs to exclude any cell that currently has a "n/a" within that cell. I need to include these cells within the formula so if/when these cells change from "n/a" to a number, they will then be considered into the total.
View Replies!
View Related
Formula If 'refill'
I have two columns of data, column A is a list of numbers and column B holds the word 'refill' at odd intervals ie: 12 14 16 17 13 12 Refill 13 15 Refill In column C i want a formula such that If 'refill' appears in column B then i get the average of all the numbers in column A since 'refill last appeared in column B. ok its an easy formula basis, i.e. =if(b1= "refill,avXXXXX,""), but i need the XXX bit, any ideas?
View Replies!
View Related
My Formula Changes On It's Own
I am using xp Home edition, and excel 2002. I have a spread sheet for my accounting that while I put data in the cells the formula will change in selected cells. I have across the top marked into 1st,2nd,3rd,4th,5th weeks. In the cells going down I have different accounts the income is given credit for. In this formula it is set up =sum(A1:E1)F1+H1. The E1 inside the( ) will change when I put the amount spent from the account. I am not using a  sign with the spent figure, being it is in the formula. the formula will change the E1 to F1 when I click enter or the up or down arrows.
View Replies!
View Related
Formula's? 3 Out Of 3, Not 2 Out Of 3!!!
I am currently trying to get M cell to disply either Pass / Fail. I want the M cell to use 3 other cells. if all 3 cells show pass then I want M cell to show a pass. If however 2 cells show pass and 1 cell shows fail, then the M cell will show fail. If 1 cell shows a pass and the other 2 cells fail I want M cell to show fail. All the other cells work ok using =IF(AND(G6<44), "FAIL", "PASS") I tried =IF(AND(K6>10.3),"FAIL","PASS")+IF(AND(I6<50),"FAIL","PASS")+IF(AND(G6<44),"FAIL","PASS") but as you can see it messed up....
View Replies!
View Related
Using More IF's In One Formula
I'm trying to make this formula work, but for some reason I'm having some problems with the result. I'm sure it's something minor. see attachment. I think the problem is with the intervals. The discount should be as follows in D11: If D7 is more the 200 (H11) then it should use D9*0.4 D7 is between 200 (I12) and 100(H12) then it should use D9*0.5 D7 is between 100 (I13) and 50 (H13) then it should use D9*0.75 and so on
View Replies!
View Related
Can't Use Numbers In Formula
I copied and pasted a text file into excel. Now I am trying to sum the numbers, but the formula always equals zero. How can I fix this so that excel recognizes the numbers. I tried copying 1 and pasting special values  multiply. This has always worked for me in the past, but its not working now. Ex. cell A1 equals 100, A2 = 50 sum(A1:A2) = 0
View Replies!
View Related
Same Formula, Why Can't Get The Same Answer??
in the "Order for EMS" and "Order for FEDEX" there have 1 product exist, now the customer need to type the country in the "F35", and the Price of weight will look back to the sheet "EMS Weight" and "FEDEX Weight" but for example, if i type Japan in both sheet, the EMS that one is correct 1kg to Japan is $168 but in FEDEX that one is not correct 1kg to Japan is $169 but now show $342, the formula in "G36" should be correct, what's wrong of my formula????
View Replies!
View Related
