Formula's Keep Disappearing- Formula Does The Calculation And Then Disappears

Jan 30, 2008

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

ADVERTISEMENT

Excel 2013 :: Formula Disappears Once It Calculates?

Jul 17, 2013

using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?

View 7 Replies View Related

Macro-created Formula Disappears Without Pattern

Mar 13, 2007

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 3 Replies View Related

Formula For Deleting A Formula When The Formula Has Done Its Calculation

Oct 7, 2009

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 6 Replies View Related

Add A Margin Calculation To A Formula?

Apr 20, 2014

I put my margin in G6 of the data sheet and the cost are the numbers on the Mail Rates sheet.

cost/(1-G6) and then round result up to next .25

=VLOOKUP(Calculator!E6,'Mail Rates'!A2:D32,Calculator!D6+1,FALSE)

I am using the vlookup above to bring the mail rates to a calculator. I want to add a margin to the mail rates before they reach the calculator.

The margin amount is in G6 of the "data" sheet and the margin formula is "mail rate"/(1-G6)

I would then like to round the result up to the next .25 dollar

View 3 Replies View Related

Formula For Calculation With Minimum Value?

Feb 28, 2012

Looking for a formula that can account for a minimum value.

The calculation is as follows 1.22*(x)+8=(y)

(y) must be greater than or equal to 20.

View 7 Replies View Related

PV Formula Different Than Manual Calculation?

Jun 26, 2013

I 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 Related

Formula For Average Calculation?

Jul 8, 2013

I have data regarding the percentage of QC for the team.

I need to find out the individual's average QC scores from the list already available.

The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage

View 3 Replies View Related

Date Calculation Formula

Jun 15, 2007

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 9 Replies View Related

Calculation Of Time Formula

Jul 14, 2009

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 9 Replies View Related

Formula: Choose The Calculation

May 8, 2006

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 2 Replies View Related

Formula Calculation Precision

Dec 14, 2006

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 9 Replies View Related

Step Calculation Formula

Apr 14, 2007

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 7 Replies View Related

Time Calculation Formula

May 11, 2007

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 4 Replies View Related

If Condition Formula For Calculation

Aug 2, 2007

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 6 Replies View Related

Reverse Calculation Of Formula

Oct 9, 2007

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 2 Replies View Related

Formula / Function For Commission Calculation?

Jul 26, 2006

if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... 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%.

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 9 Replies View Related

Excessive Formula: Add Upp A Calculation More Or Less In Infinity

May 4, 2009

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 3 Replies View Related

No Calculation Flag, And Percent Formula

Nov 13, 2009

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 3 Replies View Related

Progressive Tax Calculation Formula In Reverse

Nov 30, 2012

I came across the solution to this post - [URL] ..... - and was just wondering how to adjust the formula so that it calculates the correct tax starting from the net amount.

Say for example, an employee is paid a fixed, after-tax salary amount, and the employer is responsible for covering the tax - the salary payment needs to be "grossed-up" to calculate the correct amount of tax.

With 'tax brackets' as follows:

Taxable incomeTax on income (2011 – 2012)Tax rate
$0 – $6,000Nil0%
$6,001 – $37,00015c for each $1 over $6,00015%
$37,001 – $80,000$4,650 plus 30c for each $1 over $37,00030%
$80,001 – $180,000$17,550 plus 37c for each $1 over $80,00037%
Over $180,000$54,550 plus 45c for each $1 over $180,00045%

The formula to calculate tax, starting from the gross amount, is:

[Code] ......

View 4 Replies View Related

Calculation In Range In Formula Area?

Mar 3, 2014

I'm trying to do this:

Column A, B C
A has lets say 100 elements with certain ratings.
B has has a,b or c for each rating from column A.
C returns a for each rating from column A.

I search for a rating with this formula:
= INDEX(range,MATCH(rating;range,1,1,1))

Now I have the relative position of the rating in that range. Now I want to search for "b" in column B that reflects that rating. For instance:

Columns:
10 a 1
20 c 1
30 b 1
40 c 2
...
Search rating = 40 so C would be the answer.

But in that range A1:B4 I want to search for the position of the last b from column B

I tried this but I can't calculate the ranges:

match("b";b1:b1+x,0))
x= INDEX(a1:a100,MATCH(40;a1:a100,1,1,1))

View 6 Replies View Related

Formula For A Calculation With Varying Spreadsheet

Mar 11, 2014

I've tried an if statement with AND function with no luck either.

my spreadsheet looks like this:
A1 = "One", B1 = 17/08/2013, C1 = 1000.00
A2 = "Two", B2 = 16/08/2013, C2 = 500.00
A3 = "One", B3 = 17/07/2013, C3 = 1500.00
A4 = "Two", B4 = 13/07/2013, C4 = 400.00
A5 = "Two", B5 = 14/07/2013, C5 = 300.00

"One" represents the total that needs to be paid and "Two" represents the payments.

What I want to do is recognise when "One" is present and deduct "two" from that amount, but the whole spreadsheet is never the same (the data is being pasted from somewhere else) so it's really, really difficult to do that.

So I want to do =1000-500 (for that period, so in D2 it should say 500) and then =1500-400-300 (for the other period so in D5 it should say 800).

NB: Any payment ("two") that comes in after the 17th of the month will be carried forward to the next months "one".

View 12 Replies View Related

Formula That Doesn't Do Calculation For Blanks?

Mar 4, 2014

I have a very basic formula, and I'd like it to ignore a blank cell. The sheet has cells A1, B1, and C1. I want the formula to subtract A1 from B1, and display the difference in C1. B1-A1=C1

Currently, if A1 has a value (it always has a value in that column) and B1 does not, C1 shows the negative value of A1. Ex: A1= 100, B1= blank. [blank cell] - 100 = -100

I want C1 to show nothing unless there's a value in B1.

How do I construct the formula so that it doesn't do the calculation for blanks?

View 2 Replies View Related

Multiple IF Criteria - To Set Calculation Formula

Mar 10, 2014

Attempting to calculate assignment hours based on three fields -

A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)

I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5

My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.

View 2 Replies View Related

Need Assistance With Average Calculation Formula

Jun 15, 2007

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(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),

IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),

IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),

IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),

IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),

IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))

View 9 Replies View Related

Declining Balance Calculation Formula

Jan 5, 2006

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 9 Replies View Related

Formula/Function For Commission Calculation

Jul 26, 2006

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 9 Replies View Related

Formula To Check Date Range And Then Do Calculation?

Apr 7, 2014

I am looking for a single formula I can run along a single row that looks at several columns of data, and then checks if the date range falls between the running row, take it's relevant number, and multiply it by a single rate. The attached probably explains it clearly - I think the solution would be some sort of Sumproduct, but my attempts have failed!Example - Date Range with Calculation.xlsx

View 10 Replies View Related

Calculation/formula: ADDS Cells B1 And A1 (B1+A1) IF B1 Is Negative

Dec 15, 2008

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 3 Replies View Related

Excel Formula / VBA Code For Time Calculation

Aug 14, 2012

I am calculating the employees time and need the time spent to be categorized as follows

Monday to Friday
06:00 to 09:00 is OT
09:00 to 17:00 is BASIC
17:00 to 00:00 is OT
00:00 to 06:00 is PREMIUM

A2 =Date= 31-07-12
B2 =Start Time = 03:00
C2 = End Time = 23:00
D2 = c2-b2 = 20 hours formatted as [h]

there are situations when end date and time is the next day

E2 = 8 hours
F2 = 10 hours
G2 = 3 hours

Saturday
07:00 to midnight is OT
Midnight to 07:00 is premium

Sunday
all the time is premium

View 1 Replies View Related

Rank Calculation (with Correction) In Array Formula

Nov 9, 2007

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 9 Replies View Related

Check If X Cells Are Empty Before Formula Calculation

Dec 5, 2008

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 3 Replies View Related

Add Shape On Cell Formula Calculation Value Change

May 28, 2008

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 4 Replies View Related

Formula To Stop Calculation If Redemption Status Is Not Yet To Be Dispatched

Mar 17, 2014

I have a file that tracks orders placed.

Column A gives the request date of the order, based on the TAT in column B the projected dispatch date gets calculated.

Once the order is dispatched and the date gets updated in column Z, the redemption status in column V gets updated as "Dispatched within TAT" or "Dispatched out of TAT" based on whether they have met the TAT or not. If the dispatch date is left blank then it shows "Yet to be dispatched".

My problem is that the column D does not stop calculation even after the order is dispatched. And therefore because of the conditional formatting turns red the minute the number is more than 0. So it is misleading.

View 3 Replies View Related

Formula To Count 12 Months Back For Finance Calculation

Mar 24, 2009

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 2 Replies View Related

Formula To Skip Over That Data Point In The LINEST Calculation

Sep 4, 2009

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 9 Replies View Related

Formula Structure: The Total Fees (H2) Is A Part Of The Calculation

Apr 22, 2006

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% 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 3 Replies View Related

Formula Not Updating: Using Tools, Options, Calculation, Automatic

Oct 27, 2006

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 3 Replies View Related

Excel 2007 :: Differences Between PEMDAS Calculation And Evaluate Formula

Apr 16, 2014

Using excel 2007 and I noticed some differences between how a caluation is done via PEMDAS (as I understand it) and evaluating vai excel.

The formula is just an example and doesnt relate to anything as such.

The formula is: 2*6+3-4/2-5+20/5*3+50

using [URL] the formula show the calculation as I would have calculated.

Manual Order.png

When using excel it does it in a different order

Excel Order.png

Any reason why?

View 1 Replies View Related

Date Calculation Formula - Determine Longer Period Of Time

Aug 9, 2012

How to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.

My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)

Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2

If there is any way to include the month/year in G2 that would be ideal.

View 6 Replies View Related

Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result

May 24, 2008

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 7 Replies View Related

ADVERTISEMENT