Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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:
Macro-created 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


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
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, H50-F50+(H50

View Replies!   View Related
Formula Calculation Precision
I am getting a multiplication error in excel when I multiply 1796 X 156 the answer should be 280,176. However, I am getting an answer of 280,187 any suggestions? I am working with mutiple cells ect.

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
Time Calculation Formula
I have attached a time card that needs a formula to calculate the time for the high lighted areas.

In+out-in+out= total hours.

View Replies!   View Related
Reverse Calculation Of Formula
how can i create a formula by 3 variable?( power form: y=(x^a)*(v^b)*(g^k)*p that y,x,v,g are known and other parameter are unknown.)

View Replies!   View Related
If Condition Formula For Calculation
I'm trying to use the if condition formula however it doesn't give me the calculation?

=if(A2<20%,"B2/.80", A2>20%,"c2*.02)

My division and multiplying functions do not work or it doesn't calculate for me?

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 0-6. 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.







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 a-g (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.

Grand Peninsula=B5/N3
Meadow Glen(Mansfield)=B6/N3

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
Excessive Formula: Add Upp A Calculation More Or Less In Infinity
There has to be a way to make this more simple. I want to add upp a simple calculation more or less in infinity =SUM(($D$1*D6)+($D$1*E6)+($D$1*F6)+($D$1*G6)+($D$1*H6)+($D$1*I6)+($D$1*J6)+($D$1*K6) .. etc etc..

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 Mann-Whitney U-test calc). For example, I have the results of a survey quesiton (1-5 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)+1-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),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
Calculation/formula: ADDS Cells B1 And A1 (B1+A1) IF B1 Is Negative
How can i do this using Excel 2007. I have to cells, A1 and B1. A1 is always Positive whereas B1 might be Positive or Negative. I need a calculation/formula in C1 that ADDS cells B1 and A1 (B1+A1) IF B1 is Negative and SUBTRACTS A1 from B1 (A1-B1) IF B1 was Positive.

View Replies!   View Related
Check If X Cells Are Empty Before Formula Calculation
I have a spreadsheet that uses IF(C5>J5,(EDATE(C5,12)),(EDATE(J5,12)))__ IF(J9<D9,(D9-$N$1)) where N1 is current date. If C5 and J5 are empty how do I get it to ignore the formula and just leave the other cells blank?

View Replies!   View Related
Add Shape On Cell Formula Calculation Value Change
I am trying to get different shapes (previously created) to appear in a certain cell, dependant on the resultof a formula in an adjacent cell.

View Replies!   View Related
Formula To Skip Over That Data Point In The LINEST Calculation
I have instances where my Y variables sometimes contain a zero in the data and i need a formula to skip over that data point in the LINEST calculation.

Y variables are in Row 1, Columns A:E
X variables are in Row 2, Columns A:E

The following formula is returning a #VALUE! error: .....

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:

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.

Loan Dsc. Int. DSCNT% Y-S-P% 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 Tools-Options, that doesn't work either.

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;


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
Lookup Formula: Commission Calculation To Be Done Automatically Once Data Is Inputted In Cell
I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.

View Replies!   View Related
IF Formula: Check If F13 Cell Is "+" If Not Do Calculation (F13-E13) And Stop
I have a IF formula that check if F13 cell is "+" if not do calculation (F13-E13) and stop, if yes go to the previous line and check if the F12 is "+" if not do calculation (F12-E12) and stop, if yes continuo to the previous cell and so on until you find cell without +

The EXCEL IF formula is
=IF(F13="+",IF(F12="+",IF(F11="+",IF(F10="+",IF(F9="+",IF(F8="+",E8-F8),F9-E9),F10-E10),F11-E11),F12-E12),F13-E13). I'm looking for VBA code with FOR to run and do the same without the limitation of 7 if inside if.

View Replies!   View Related
Sheets Keep Disappearing
I have a shared file in excel and it is used by up to ten users, lately sheets keep disappearing and (of coarse) no one knows anything about it. Does anyone know how this is happening? Or what security protocol I can use to keep it from happening?

View Replies!   View Related
Disappearing In-cell Instructions
I'd like to have a cell contain instructions that disappear when text is entered into the cell, much like the "help" box on the upper right of the Excel window has a label that says "Please type question here", which disappears when you actually type in a question.

View Replies!   View Related
Disappearing Addin List
I have workbook which automatacally 'installs' an addin, i.e. ticks the box.

When I open the workbook without Excel running, the code fails to install the addin. When I then use Tools > Addins, the panel of addins is completely blank.

I can only restore the addins list by closing Excel and reopening it.

View Replies!   View Related
Disappearing Data Validation Lists
I have a very large spreadsheet that I work with on a daily basis. The main portion of the spreadsheet is broken down into weekly sections (for example, columns GF thru GM contain data sections for one week, which then repeats with the next set of columns, etc.). Below these weekly sections are detail and summary sections for all the data entered above. In the main weekly portion, each week contains a size drop-down list which calls to size information in the detail portion below. Recently, the data validation lists have started disappearing. By disappearing, I mean that the data validation for the cell no longer exists on a large portion of the list cells. I have tried recreating all of the data validations, saving the file, and re-opening it, and when I do, they've disappeared again. I have no idea what to do at this point. The functionality of the spreadsheet is crippled without the sizing drop-down lists (for various lookup reasons).

View Replies!   View Related
Custom Right Click Buttons Disappearing
Have a spreadsheet wherein I have added some custom controls to the right-click function of the mouse. The buttons and their associated code runs great for all but one user in my office. For that user, the custom buttons do not even appear on his right-click menu. I have looked through his settings to make sure that his security settings are the same as everyone else that uses the spreadsheet, and they are. All of the other modules in the spreadsheet work fine.

Is there some other setting that would control the display of his right-click buttons. Perhaps a windows security setting that I am missing?

The following is a snippet of the code that adds one of the right-click buttons for example. This code is stored in the 'Workbook_SheetBeforeRightClick' event.

With Application. CommandBars(" cell").Controls _
.Add(Type:=msoControlButton, Before:=1, temporary:=True)
.Caption = "Create Panel Hyperlink"
.OnAction = "CreateHyperlink"
.Tag = "brccm"
.FaceId = 2169
End With

View Replies!   View Related
Disappearing Data Validation Dropdown
All of a sudden, the data vaildation dropdown boxes on a specific worksheet have disappeared. The list reference is still correct and the data validation dropdowns work just fine on other sheets in the same workbook. (BTW-I'm using Excel 2003 and the panes are not frozen).

View Replies!   View Related
Result Arrives And Disappears
I begin in the Word of VBA. It's the first time I've a formula which seems necessitate VBA to be written down.

Before to try to execute my complicate macro, I've begun with simple tricks. But I've got a strange comportment of Excel :

I've the following Sub :

View Replies!   View Related
Scroll Bar Disappears Clicking It
I have a chart that I just added some dynamic text boxes to. That is the text is linked to a cell so that the text is automatically updated.

I also have a scroll bar that is used to adjust one of the cells. However, now once I click on the scroll bar it just disappears. I can click on the space where it is located and it will appear for a split second. Also, if I scroll down in the worksheet and then back up over the scroll bar the image will be refreshed and I can see the scroll bar again.

I didnt have this problem until I added the dynamic text boxes, it worked fine with static text boxes.

Does anyone have any idea how to get around this, or do I have to stick with the static text boxes?

View Replies!   View Related
Autofilter Disappears When AdvancedFilter Used
I have an auto filter setup on some data. When I perform an advanced filter either in-place or to another location, the auto filter disappears. Is it supposed to be have like that? If so is there some sort of work around?

View Replies!   View Related
Userform Disappears After Clicking No
i got the userform to popup when the workbook is open and the buttons work fine... only problem is when i click on a button, it will ask the user yes or no... if the user press yes, it performs the required macro without any problem... but when the user press no, not only it will close the question, but also closes the userform... I would like the 'no' to close the question but not the userform...

View Replies!   View Related
VBA: Pop-up Calendar Disappears When Script Stops
My Workbook contains the following macro in Sheet 1, which displays a pop-up calendar in L15 when that cell is selected:

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("L15"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub.........

View Replies!   View Related
Text Disappears When Word Wrap Is Used
Whenever word wrap is applied the text is not visable in the cell. It
remains visable only in the bar above.

I have already confirmed autofit and it reduces the column to 1 character

I tried detect and repair on the installation and nothing.

I even upgraded from Excel 2k to Excel 2003 and no change.

View Replies!   View Related
Inserted Picture Disappears After Losing Focus
I have the same problem as Paul C in the following post:

(Pictures disappearing)
"Hi not a VBA problem, but when i insert pictures or logos into my workbook they disappear, i can insert a picture close the workbook but when i re-open it the picture has gone, i have to click on where it should be to get the border to appear, the move it to get it to appear.

I have tried inserting BPMS, JPGS, GIFS, but i have the same problem with them all, I have used the bring forward command but still have the same problem, I have even tried re-installing Office 2003 and installing all the latest updates but still get the same problem, any ideas?"

I have tried:

- Tools > Options > View > Objects > Show All
- Changing macro security level
- disabling macro security in my antivirus

View Replies!   View Related
Locals Watch Disappears; Program Crash
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close". The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this. A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).

Option Explicit
Sub Compare()
Dim strNaspK As String
Dim strCtryK As String
Dim intCtryRev As Integer
Do Until ActiveCell. Offset(1, 0) = ""
ActiveCell.Offset(1, 0).Select
strNaspK = ActiveCell
strCtryK = ActiveCell.Offset(0, 2)
On Error Goto ErrJump:........................

Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.

View Replies!   View Related
Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula
Here is the scenario:

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take

and produce:

View Replies!   View Related
Vlookup Formula In VBA - Object Error (input A Formula Into A Cell)
I'm having some trouble trying to get excel to input a formula into a cell. I'm still a novice at VBA right now, so I don't think my problem will be too much of a brain buster.

I want a formula in Cell A6 (and I already know it correctly works) in this format: =E6&VLOOKUP(I6,'FA-Fund Data'!B$1:C$2000,2,FALSE)&J6

View Replies!   View Related
Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.

i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6

this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads

I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire

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
Copy A Formula In A Cell And Then Paste Only The Text Of The Formula
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.

When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.

View Replies!   View Related
Formula Arrays That Will Omit Empty Cells In My Formula
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.

View Replies!   View Related
Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range.

I want to isolate one range, Col N, and add an IF condition to it.

There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....

View Replies!   View Related
Not Responding: Added A Formula To A Spreadsheet With Som Complicated Formula
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.

View Replies!   View Related
Link Formula / Update Formula Automatically
Im working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.

So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).

Im wondering if its possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).

I can copy the formula with the Past Special function but the function is not updating if I change the formula on the main sheet.

So is it possible to link it and that itll be updated automatically when Im changing the formula on the main sheet?

View Replies!   View Related
SUMIF Formula That Is Collects Values From An IF Formula
Can anyone tell me why this formula is not working?

=SUMIF($D$6:$AL$6,"=TM Goals",D12:AL12)

The entry under cell T12, which I am hoping this formula will pick up is based on the following formula.


View Replies!   View Related
Copyright 2005-08, All rights reserved