Calculate End Result Based On Cell Contents Of 3 Columns
Jan 13, 2014
I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F
For example:
Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7
I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]
Here are an example of what I have tried, amongst many...
=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
I am trying to get result in a column based on contents of previous 4 columns. For example as per table below, if there is value 1 in either of first 4 columns of same row, I want to display A in column 5. As first 4 columns in row 2 to 4 contains 1, result is A. If there is any 2 in first 4 columns, I want to display B. If there are both 1 and 2 in any first 4 columns, I want to display C. If there is 3 in any of first 4 columns, I want to display D. And if all 1, 2 and 3 are in any of first 4 columns, I want to display E.
I frequently review DAT files. These DAT files use a variety of characters as delimiters. I'm trying to devise a macro that will perform a text-to-column operation, using the content of a specified cell as the delimiter.
Here's my code, which uses - (ASCII 0254) as a delimiter:
Code: With Sheet1 Range("A1", ActiveCell.End(xlDown)).Select Do
[Code]....
Instead of having the delimiter hard-coded, I'd like my macro to use whatever character I enter into Sheet3.Range("i5") as a delimiter. That way, I can easily change my delimiter character as necessary.
I've been trying to loop data for column A from row 1 until 6 and print the result on specified cell. But the problem is, it keeps looping until column B,C and D.
I want to calculate average result separately for column B,C, and D without taking the previous data.
Here is the code that I've done.
VB: Sub Avg() col = 1 Row = 1 Do Name = ThisWorkbook.Sheets("Sheet2").Cells(Row, col).Value If Name <> "#" Then
[Code] ....
I attached my excel. The green box indicate the result that I want : function.xls
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I would like to be able to round off the decimals resulted in the POV field on my worksheet and have a running total of the entries listed below. This occurs sometimes when the mileage units have decimals. In this example, the POV values display $1.00, 99 cents, and 93 cents. Their resulting total should be 2.92, but Excel calculates them as $2.93 due to the additional decimals.
The problem is that if I apply an =ROUND function to the formula in the POV field, while it will display a correct answer if all the date and mileage fields are filled in for which the formula has been applied, it will not give a running total in instances where there are empty date/mileage fields, reporting "VALUE!". Is there a way to force Excel to display the running total?
I have attached the worksheet, with the example above entered in. I have included the formula I've been trying to fix on my worksheet on the bottom as well.
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
| ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
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.
If I have the following: =SUM(E2:F2) - G2). So lets say that E2 has 50, F2 has 60 which = 110. Now I want to take that 110 and subtract it from what is in G2 and put the result in H2.
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.
I assume there is an array formula that could do this? I've tried a few solutions with no luck.
I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.
As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.
When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet. -----
WHAT I NEED TO DO: Select some cells (must be in the same row - that we have just pasted in). Press a button on the Worksheet labelled "Auto Assign Desk", which does this:- For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.) -----
This will assign the employee to the desk they were sat at last, when they are in the office next week.
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
I have two sheets: - sheet 1 ('current') - sheet 2 ('31May')
The data in column C is the same, although sorted differently and can't be re-sorted to be the same. Where the data in column C is the same in both sheets, I want to fill columns A and B into sheet 1.
I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25.
Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank. Cell 'B1' has a drop down to select specific items. Cell 'C1' is the date of the order and is a locked cell. Cell 'D1' is the date of completion and is a locked cell. The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date. If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
Is it possible to dynamically chance the name of at a worksheet that already exists based on the contents of a cell on another sheet?
I.e. If the name of a sheet is linked to the value of the Cell A1 in the "Data" sheet and I change A1 to read "Group1" it changes the name of the sheet as well?
I am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.
I wish to be able to lock an entire row after an entry has been made in a certain cell.
e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.
I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front.
I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.
I have thousands of items each with a unique barcode(column A) and each is catorgorized either KEEP or DISCARD (column B). What I want to do is scan the barcode (column A), Vlookup and return KEEP or DISCARD (column B). Now based on what the vlookup returns can I have a sound file play for KEEP and a different one for DISCARD.
