Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Mega Formula To Calculate Results Based On Several Conditions
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking. For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05) The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Calculate Ranking Based On Results Of 2 Columns
I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD) In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
View Replies!
View Related
Calculate Formula Based On Cell Criteria
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible? I have attached an example of what I am trying to do.
View Replies!
View Related
Formula Required To Calculate Points For Football Results Prediction
I'm creating an (English) football predictions competition for me and my family. One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions. The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
View Replies!
View Related
IF Function (few Words Shown Based On Figures In Specific Cells)
I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best: A1 = 5 A2 = 0 A3 = 7 The function i am using looks something like this:- =IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?") However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well. Maybe it should look something like this but it does not work the way i am doing it. =IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")
View Replies!
View Related
Formula: Absence Detail Shown
I am trying to re-jig a formula that shows hours worked for a particular day, to showing any absence detail shown for a particular employee. I have the fllowing formula which doesn't work and I'm not sure why. A similiar formula works for me getting basic hours worked etc =SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail)) My range names are correct and all have the same 'length'. F67 is 09/05/05; StaffNumber = 100 and the value that is within the AbsenceDetail cell for the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it formatted as General)
View Replies!
View Related
Formula To Calculate Based On Criteria
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column. The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis. I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
View Replies!
View Related
Return Results Based On Referenced Cell
I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)
View Replies!
View Related
Formula To Calculate A Fee Based On Performance
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance: KEYPerformance 2010,000 2120,000 2230,000 2340,000 I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges: KEYMin FeeFee 1Fee 2Fee 3Fee 4 201000.10.20.30.4 211000.10.20.30.4 221000.10.20.30.4 231000.10.20.30.4 For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows: Fee Range 10-9999 Fee Range 210000-19999 Fee Range 320000-29999 Fee Range 430000-99999999 So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest. How can this be combined into a lookup/range/low-high formula to extract the correct values????
View Replies!
View Related
Formula To Calculate Price Based On Sheet2
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged. Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution. Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View Replies!
View Related
Calculate Colum Total Based On Formula
I would like to have something that looks like a running total, but isn't. I have in a colomn 'rate' and after that a column for each month in a year. I would like to have at the bottom a total cost, so rate x hours. have a look at the picture. is there a formula for this? ratemayjunejuli $10 2,02,01,0 $12 2,03,00,0 $10 3,02,05,0 $15 4,02,53,0 total cost $134,0 $113,5 $105
View Replies!
View Related
Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example Hours ST OT Total Hrs Gross Pay 8 2 10 ? i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
View Replies!
View Related
Formula With If Condition: Calculate Bonus Based On Productivity
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View Replies!
View Related
Automated Worksheet Copy, Rename Based On Cell Results
I am building a workbook. The data we are tracking is all in one xml file, which i have mapped to 3 different worksheets(customers, invoices, inventory). On the customers and inventory sheet I have an interface for creating a new customer/product/invoice. I used the macro recorder to make the macro's to do these three things, but could use help on a couple of other functions as I don't actually know VBA. I need to be able to automate editing of existing records by having a button to press on the each sheet that will open an input box that asks the user which invoice number, customer number or product number he would like to edit, then copy that record to the interface for editing, then another macro to replace the existing data with the newly edited data. It would also be wonderful if when creating or editing an invoice it could create a copy of the interface worksheet and rename it as the invoice number. The first row of each datasheet is blank, and each of the interfaces have formula's for importing the information copied to row 1 from the data tables. the second row of each datasheet contains formulas for importing data from the interface. Any help would be terrific...I know you guys are excel gods and I will forever be in your debt if you can help me out....thanks in advance, I'll be studying my butt off until I figure this out
View Replies!
View Related
Adding Numbers In A Column Results In Zero
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result. Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the formula doesn't work? This happens on some sheets in my workbook, but not all sheets. Also, another wierd thing: on some cells doing the above reults in the formula showing as text in the cell.
View Replies!
View Related
Copied Formula Show In Cell Not Results
I have copy 65'000 formula text cells from a software and paste it to excel. But, the formula in cell is not activated. In order to activate the cell formula, I have to click on each individu cell and press enter to active it. But i got 65'000 rows of the similar cells. So, what is the quick way to all the text formula at the same time.
View Replies!
View Related
Adding Worksheets Based On Cell Criteria
I would like one column (column A) to be an "indentifier" column. Column B is the customer name, column C is the customer city and state, column D is the customer phone number, and column E is the customer email. By calling column A an "identifier" column, what I mean is that I would like to be able to go down the list and put an "x" next to each customer I would like to include in that particular workbook. I would then like to be able to run a macro which would take each of the customers with an "x" in the column A, create a copy of the template comfirmation call sheet, and fill in the identifying information from columns B through E into the copied template. Each worksheet would also need to be renamed the same as the customer name. It would need to perform this same procedure for each row that has an "x," making sure that all worksheets (confirmation call sheets) are grouped into one new workbook. Sheet 1 is called "main" Sheet 1 contains 5 columns A = Blank B = Customer Name C = Customer City and State D = Customer Phone E = Customer email Sheet 2 is called "template" "x"'s are placed into "main" sheet, column A to identify which customer will be affected by macro...................
View Replies!
View Related
Results Show Results Of Formula, But Should Be Blank
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View Replies!
View Related
Adding Cells Based Off A Set Numeric Value From Another Cell
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it. I want a formula to add cells based off a number value in another cell. So it would be something like IF M3= 1 add C5 to SUM ; If not then go to next step IF M3= 2 add F5 to SUM ; If not then go to next step IF M3= 3 add I5 to SUM ; If not then go to next step IF N3= 1 add C5 to SUM ; If not then go to next step IF N3= 2 add F5 to SUM ; If not then go to next step IF N3= 3 add I5 to SUM ; If not then go to next step....................
View Replies!
View Related
Calculate Autofilter Results
I have a list of data in Excel with Autofilters. At the bottom of the data set is a row that totals up many of the columns. Whenever I use the Autofilters, the totals at the bottom still reflect the entire set of data, rather than only the filtered data. Is there a way (using macros, formulas, or something else) to make the totals automatically update to only reflect the data that is visible when using Autofilters?
View Replies!
View Related
Calculate Filtered Results
I am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out. I get the error "Unable to get the Sum property of the WorkSheet function class" This is the code I have butchered: Set R = ActiveSheet. Range("d1"). CurrentRegion Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count) For i = 1 To R.Rows.Count If Not R.Rows(i).EntireRow.Hidden Then intCountCancelled = WorksheetFunction.Sum("Cancelled") ' cancelled is a named range. This is the line its failing on End If Next i
View Replies!
View Related
Adding Text In Same Cell As Formula?
I have a list of numbers in column A (i.e.: 1234)and I need them to show up in column B in with an "*" asterisk on each side on the number (i.e.: *1234*). So I was using "=a1" in cell B1, is there away to add the asterisk to the formula as text?
View Replies!
View Related
Calculate Matches, Total Results
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
View Replies!
View Related
Adding Multiple Row Contents Based On The Content Of A Single Cell
Been trying to work this one out and have ended up banging my head against the wall. Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability. The team name is a selection from a list, the availability is 1-6 (different levels of availability) I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up: Name Team 1st 2nd 3rd 4th 5th 6th 7th etc. Mr X ___1st___1______2__1 Mr Y ___2nd_______2________5__5__5 Mr Z ___1st___1____________3__3__3 Total 1st: 2 2 1 3 3 3 Total 2nd: 2 5 5 5 So basically the formula in the last row goes along the lines of: Add each cell in the column IF the team name = 1st
View Replies!
View Related
Cell's Are Not Lining Up Correctly
I have used the Excel program for a number of years to print out "Jukebox Title Strips" for my Jukebox. I have used the same "formula" for cell height, width, etc. during that time. The outline of the title strips are preprinted and perforated on an 8.5" x 11" sheet. I recently tried to make some new strips and the printing does not line up correctly as it always had done in the past. Now, the titles are not high enough on the top portion of the sheet, and not low enough on the bottom portion of the sheet. So, it is not that I could just raise everything up or lower everything down to line up correctly... it now has to be changed in both directions. Of the 20 title strips on the sheet, the only ones that line up correctly are the two that are halfway down. I thought that somehow the file had become corrupted, so I made a new template, but I still have the same problem. Are there any suggestions of what I could do? In the links below it shows an example of a Title Strip page.
View Replies!
View Related
Automatically Calculate 2 Cells Based On Cell Changed
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself). Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10. I really do not think the actual formulas matters at this time... Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
View Replies!
View Related
Calculate X Number Of Rows In Range Based On Cell Value
I can't seem to make SumIF or Vlookup do what I want here. I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column. If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3) Period Target Actual %Target 1 74 68 91.9% 2 81 71 87.7% 3 76 87 114.5% 4 76 68 89.5% 5 71 89 125.4% 6 69 81 117.4%
View Replies!
View Related
Rightclick Macro If Cell Contents Formatted Correctly
I'm attempting to create something that can be used on any workbook open to automatically open a file based on the data in cell. 1) User right clicks on a cell. 2) If the cell's value matches an expected format (##-A-####-## for example), a macro is available in the rightclick popup menu. 3) The user selects the macro in the popup menu if they want to run it. 4) The macro takes the cell value and converts it into a filename. 5) The specified file is opened in an external program.
View Replies!
View Related
Import Log Data, Calculate And Displaying The Results In Several Charts
I have a Template which is used to import log data, calculate and displaying the results in several charts. Then all sheets are copied to a new workbook, a Save As dialog apears for the new workbook and the template will close. This is all done with VBA and it works fine. Problem: The Worksheets with the Charts on it refer to the data-source at the template file. Strange because the copied worksheets have the same names as the originals. (when Opens: This Workbook contains Links to other Workbooks...) Question: How can I establish that the Charts will point to their own data sources in the same Workbook rather then linking to the Template file on a Server.
View Replies!
View Related
Formula To Calculate Cell Location
i'm not sure how to describe this. i've never had to do this before. here is what i'm trying to do. and it's not working. =MIN(B2:B(G2-G3)) basically instead of saying =MIN(B2:Bx)....i want the "x" to be variable based upong the G2-G3 condition. can this be done some other way?
View Replies!
View Related
Copy Cell Reference From Seperate Sheet To Increment Correctly
I have two sheets in my workbook, and I am trying to reference one chart's value into the other. (attached) I have referenced successfully by manually typing in the reference on row 2 for all values on Sheet 1: Sheet 1 - C2 needs to reference Sheet 2 - B2. (Sheet2!B2) - no problem all the way across to F2. All those values are correct b/c I manually typed in the reference. The problem comes when I just want to drag over that same formula to increment for the other cells. It's not incrementing the formula like I need it to. So, Sheet 1, Row 2 shows how the correct values I need to pull over but was done in a manual way. Sheet 1, Row 5 shows how it increments incorrectly when I try to just drag/copy the formula over. What I need is for Sheet 1 - D5 to reference Sheet 2 - C3, but instead if I try and drag to copy the formula instead of manually typing it in, it increments when I drag reference D2 instead of C3.
View Replies!
View Related
Check & Change Cell After Adding Formula Via Macro Code
The loop is putting the correct formula into the cell - but THEN... - I wish to (within the loop and before it moves on) check if the value is equal to OR greater than 1 - if it is, make it a value of 1 (this '1' is then used at the bottom of the column of data to give a total). I need to do this before it moves onto the next cell. What am I doing wrong? Is it because once it puts formula in the cell, it then moves down - do I need to select the cell again first? As I had originally posted on this site - link: url]http://www.excelforum.com/showthread.php?t=641970[/url]
View Replies!
View Related
Adding # Of Months To Calculate A Date
Is there a way to add # of months to a date and then find out what would be the exact date? For example if cell A1 = 04/30/07 and Cell B1 = 14 what is the statement that would give me 06/30/08 in Cell C1? Also, how can I modify the statement if Cell B1 = 14.5 to get 07/15/08?
View Replies!
View Related
Summary Sheet Calculate Results Change If Rows Added/deleted In Detail Spreadsheet
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation. I'm using dynamic name ranges, as follows, for the detail: WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1) Other detail data is defined as these examples show: GM_X =OFFSET(WO_Num,0,8) OpenDate =OFFSET(WO_Num,0,1) All detail data begins in Row 6 in the detail spreadsheet. In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand. Here are two examples of the calcs I'm using in the summary: =COUNTIF(WO_Num,">0") =COUNTIF(GM_X,"X") Can someone tell me what is going on? What I'm doing wrong?
View Replies!
View Related
|
TRACKER
