Incorrectly Calculating Every Tab The Same?
Feb 6, 2014
I have a several tabs that are each named the abbreviation for an element (i.e. Al, Sb, etc.) and I am trying to write a formula to display the full element name based on the name of the tab and a table in another sheet. I have written the formula below which works when I enter the formula and press enter on each sheet, but when I click "Calculate Now" to run the calculations for the whole file Excel will return the name of whichever element I last calculated manually (click in the formula and press enter) on every sheet. Why does Excel calculate correctly when I press enter but then change it when I calculate the whole file?
=INDEX(MCL!$A$1:$C$28,MATCH(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))),MCL!$B$1:$B$28,0),1)
*the table "MCL" that it is looking in has the full element name in column A and the abbreviation in column B
View 3 Replies
ADVERTISEMENT
Oct 22, 2008
This is a two part question. I thank any help that can be given in regards to my problem. I have attached a spreadsheet similiar to what is used at work. We gather this information from a report we use.
Part one- in Column F, we have it set up to know how long the customer is deliquent. Column F is the difference between the date in column D and the date that is in cell E2. We are using the formula networkdays which will not count the saturday and sundays of the weeks.
The problem is, that sometimes when we place the information from the report, the value in column F is off a day, which causes us to have to adjust the formula so the information is the same. Why do we have to adjust the formula?
Part two - In the networkday formula, we do not want to include certain holidays, which is listed in column J. Is there a way to have the dates for the holidays auto advance if the dates in column J is less than the dates in column D?
View 14 Replies
View Related
Jan 16, 2014
If I enter 1 in a cell, 1.1 below, select both and drag down, I should get a vector: 1, 1.1, 1.2 etc.
However, in the most recent instance of seeing this problem, at 6.5 I get 6.50000000000001! 6.6 onwards is then correct, at 7.2 the value is again incorrect in the 14th decimal place, and the errors continue intermittently through the sequence.
Is there a fix for this? It's extremely time consuming to have to check each auto-incremented array like this.
View 4 Replies
View Related
Nov 6, 2009
How do I combine these formulas for one cell?
=IF(H16170,"Check your Entry","")
=IF(I16215,"Check your Entry","")
=IF(J16190,"Check your Entry","")
=IF(K16240,"Check your Entry","")
If any of these four cells are entered incorrectly I just want the message displayed. I put this formula in the cell I want the message displayed:
=IF(H16170,"Check your Entry",IF(I16215,"Check your Entry",IF(J16190,"Check your Entry",IF(K16240,"Check your Entry",""))))
That works except "Check your Entry" is diplayed until all four cells are filled. I don't want the message displayed with a zero value.
View 9 Replies
View Related
Nov 18, 2013
I've recorded a macro in which I unhide certain columns, copy and paste some information then hide those columns again. The problem is that when the macro is finished, it incorrectly hides columns K to AN. I did not record that and it's not in the code so I'm lost as to why it's happening.
The macro is as follows :
Code:
Sub COPY()
'
' COPY Macro
'
'
Application.ScreenUpdating = False
Columns("K:BT").Select
Selection.EntireColumn.Hidden = False
Range("M5:N24").Select
Selection.COPY
[Code]...
Why it's hiding everything from K to AN. I've tried recording the macro several times but it's just not working, no matter the order in which I hide columns when recording it.
View 5 Replies
View Related
May 23, 2007
to getting accurate regression coefficients from a dataset with *small* (standard) numbers, which contains centered predictors. More specifically, I have a dataset with 18 observed data points containing a criterion (y), a centered predictor variable (x), another centered predictor variable (z), and the interaction of the two centered predictor variables (xz). This multiple regression equation is structured to test for interactions between the two continuous predictor variables (x and z) as prescribed by Aiken and West (1991) in their classic book.
When I run the regression in Excel with the centered predictors, some of the regression coefficents in the output are estimated to be 0, although they are clealry *not* 0 as estimated by SPSS 14.0.2. I have spent many hours troubleshooting this problem (and searched many forums on the internet) and still do not know why this is happening.
Initially, I thought the problem might have to do with the cross-product of the centered predictors, but even just doing a regression with one of the centered predictors (for certain centered predictors) yields a regression coefficient of 0 (although it should be non-zero as per SPSS 14.0.2). When doing these multiple regressions with non-centered predictors, all regression coefficients are estimated accurately.
I was wondering if anyone had any insights on why I am experiencing these problems.... If anyone wants a sample of some test data I have used to troubleshoot these problems, you can download a file from: [url] and/or email me at (email deleted by Mod) for more datasets or questions.
View 5 Replies
View Related
Jan 17, 2014
I entered exactly 113,876.92 in cell L16 I entered exactly 113,390.02 in cell L17 I entered =L16-L17 in cell L18 L18 incorrectly shows the result at 486.9000000000009000 (note the extra "9" after the 11 zeros). When I expand the viewable digits on L16 and L17, they have ALL zeros after the cents. (I went out at least 25 digits). I can't be the first one encountering this.
View 4 Replies
View Related
May 1, 2014
I have the macro below which opens csv files stored in a user selected folder and processes them changing the date format in column D from DD/MM/YYYY to text stored as YYYY-MM-DD.
For most of the dates the code works without issue, but for some (those with a month <12 possibly) it transposes the MM and DD incorrectly.
I understand that when opening the CSV's in excel it automatically converts the dates to DD/MM/YYYY, so I'm actually opening in wordpad which displays as YYYY-MM-DD, with only a portion being incorrect.
I've attached a couple of sample files (pre and post conversion).
Status_20post.csv‎
Status_20pre.csv‎
View 9 Replies
View Related
Nov 4, 2009
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 Related
Mar 17, 2009
I 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.
View 9 Replies
View Related
Oct 25, 2007
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.
View 14 Replies
View Related
Mar 1, 2008
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.
View 12 Replies
View Related
Nov 9, 2008
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?
View 7 Replies
View Related
Dec 5, 2008
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 Related
Mar 10, 2009
I 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
View 2 Replies
View Related
Apr 17, 2009
Problem - billing spreadsheet for prisoner fee.
1 - 8 hrs = $55
9 - 24 hrs = $55 + $65 or $120
Anything over 24 hrs - $65 for each additional (24 hrs) ($185)
So if you were locked up for 6 hrs it is $55. If you were locked up for 18 hrs it is $120. If you were locked up for 28 hrs it is $185. And if you were locked up for 49 hrs it is $250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.
View 5 Replies
View Related
Jun 9, 2009
In order to compute and forecast an estimate of the triennial rent adjustment, i need to compute the rise in the cpi (table) from the lease date (cell c3) and the present date (cell a2) or the closest publishe date existing in the table. The % rise is easy to calculate present index / opening index -1 = % rise in cpi. I update the index monthly when onthe bls publish it.
View 3 Replies
View Related
Jun 20, 2009
I input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.
View 9 Replies
View Related
Nov 12, 2009
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.
View 14 Replies
View Related
Nov 24, 2009
I had an excellent response last time I posted here, this time I’m stuck again with a new formula. I’m trying to calculated amounts between different times, but keep tying my self in knots with complicated IF formulas.
Is there an easier way to work out hours worked between 2 times, but too complicated things further I need three separate amounts so I’m guessing I’ll need three separate formulas
Hours between 00:00 – 06:00
Hours between 06:00 – 19:00
& hours between 19:00 – 00:00
An example could be, 05:00 – 20:00 should be 1,13,1
View 11 Replies
View Related
Jun 20, 2007
I have run into a problem I have not found a solution for surprisingly. I need to calculate several fields on the form prior to saving it and thus exporting the data to the worksheet. All I can find is how to calculate the field in a worksheet and then reference it with VB code on the form. Is what I want to do possible?
If I run into many more problems I may want to shop this project out as my schedule barely allows for the work I have let alone attempting to learn VBA on the fly.
If anyone is interested I can post the project so far along with the detail of what I want it do in the end.
View 9 Replies
View Related
Apr 1, 2009
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.
View 13 Replies
View Related
Jul 27, 2009
I have a list of hotel IDs in one column of my spreadsheet. In the next few columns, I have ratings that customers gave the hotel for cleanliness, location, room, etc.
What I need to do is calculate the median of all the ratings for each specific hotel, in a separate column.
An example of what I have: ...
View 8 Replies
View Related
Jul 26, 2006
I'm sure this will be an easy calculation for most of you...........
If I have two numbers, ie. 390 and 217, how can I calculate the difference
between the two figures as a percentage? (These two figures represent sales
in two months and I need to know the difference in percentage terms).
View 10 Replies
View Related
Apr 3, 2008
I'm looking to calculate OT wages when they happen vs only at the End of Week totals. ie... if the employee hits 40 hours midshift on a Wed, I want to calculate what the total dollars would be for Wed.... a few hours at regular time plus what ever hours above 40 at time and a half.
View 9 Replies
View Related
Feb 12, 2012
I've made a spreadsheet that allows the user too enter their clock in and out times in a day for a week, it allows you too fill in two weeks worth (because we get paid fortnightly). It automatically calculates each week's pay once it has been filled in, all I need is a formula too calculate how much they would be paid after tax. I can work how much they will be paid for the 2 weeks befor tax, but I just need a formula too deduct tax from this.
View 9 Replies
View Related
Jan 9, 2013
I have 3 columns
Column A is a list of suppliers
Column B is a list of purchase orders (there may be several purchase orders for each supplier)
Column C is a list of values
I am trying to show the top 10 suppliers in terms of value, not sure on the best way to go about it
View 3 Replies
View Related
Dec 24, 2007
I'd like to calculate the r-sqaured of the S&P 500 performance in an election year to see if that has any effect on the market's performance.
The data I have is below. The election years start in 1928 and are every 4 greater than that. How would I set it up to use the rsq forumla?
S&P 500 Index
192612%
192737%
192844%
1929-8%
1930-25%
1931-43%
1932-8%
193354%
1934-1%
View 9 Replies
View Related
Mar 2, 2008
I work at a library and have been asked for help by the Asst. Director. The problem is, I have next to no Excel experience, just what I've been reading in the Help files and Online.
We are using Excel 2003 and running XP. Here is his predicament: he has an excel document that lists the books we have ordered from a publisher. Column I has the MSRP for the book and Column J has the discounted price for the book. We are trying to get Column L to show the percentage of the discount that we received. This is what i've come up with:
example for line 255 typed in as formula for L: =1-7.95/14.98
It comes out correct when I type in the formula as I show above, but when I try typing in the formula (as it should look:=1-J255/I255) , i get an error message: #VALUE! ...
View 9 Replies
View Related
Apr 18, 2008
I have a list of ages 10-65 and then different number of participants associated with each age, ie: 10 - 5,071, 11 - 6,069, 12 - 8,465, etc. to age 65. I am try to calculate the median age of all participants.
View 9 Replies
View Related
