I am doing a financial analysis for a hospital. I want to know how to add something to a number based on conditions. Base pay is $300 for the first 10 patients. If more then ten patients >11-20 add 40 dollars per visit if greater than >21 add 50 dollars per patient
View 3 RepliesI'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.
View 14 Replies View RelatedI work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas.
The data is imported into excel from a database and therefore is constantly growing.
What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service.
Because the data is ever expanding I would like this on a separate sheet from the actual data.
I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
In cell G2 I have a formula that adds the values in column C that are labelled in column B as either 'Apple' or 'Grape'. This is accomplished in the formula using {'Apple', Grape"}. Is it possible to change the formula so that the {'Apple', Grape"} can be replaced with a reference to a range in which the names to be used in the formula will be listed (for example in J1:J3 in the attached file)?
Book6.xlsx‎
I need to add a condition to a statement that adds a "If TRUE" reference to another cell.
The other cell can be either a TEXT value "RM1" OR "TE2". If neither condition is true than the value is blank or zero.
So right now I have in the destination cell =IF(AB7="CONT",V7,"") That works lovely, but really isn't countable unless cell "Z7" ALSO has Either "RM1" OR "TE2".
If cell "Z7" has the text "DEA" or "SP" then the value of "V7" will be placed in a different column. I will use a different destination column (for this modified "DEA" or "SP" condition)
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
Example: give me the value in cell e1 if cell b1 matches AND
File 1
A B C
1 H12377 03/05/2013 123
2 H12377 03/27/2013 276
3 H32389 05/03/2013 335
File 2
H12377 03/27/2013 _________
how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?
I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.
View 5 Replies View RelatedI am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View 9 Replies View RelatedI am trying to sum numbers based on three counditions.
My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*
The yellow cell contains the formula I am using.
I'm creating stockportfolio's based upon historical stock data. I have 300 stocks with monthly observations. Stocks are assigned to a portfolio based upon their return in the previous 6 months. I need the top 10% stocks in terms of return in previous 6 months (so 30 stocks) in one portfolio and the lowest 10% stocks in terms of returns in previous 6 months returns (so again 30 stocks) in another portfolio. Each portfolio is just a sum of the returns of the stocks that are in there.
First sheet: rows contain the return a stock had in the 6 months before the month in the left column (so january 2006 contains the return a stock would have had from july2005 -december 2005). This is done for 300 stocks. Second sheet: contains the return the stock had in that actual month (so january 2006 just contains the january 2006 return) The third sheet is where I want to create the decile portfolio's. This means that for every month i am creating 10 portfolio's: Each month "portfolio 1" should contain the sum of the returns of the stocks with the 30 highest returns in the past 6 months. "Portfolio 10" should contain the sum of the returns of the stocks with the 30 lowest pas 6 month returns. (30 is 10% of 300: that's why it is called decile portfolio)
So I should sum things from sheet 2 with the condition referring to sheet 1 where the returns of the past 6 months are displayed. I am trying formula's like =SUMIF(RANK...) but am getting no results. Somehow excel should select the right stocks and sum the 30 returns. I'm totally lost after a lot of trials.
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.
The following is a table which shows a salesmans sales. He or she has sold to 8 customers.
What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.
1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40
2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25
3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.
The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.
So I need to return something like this: ...
I am tasked with making a matrix that I cant seem to solve.
I have solved it on a small scale using
=SUMPRODUCT($E$5:$E$19,--ISNUMBER(SEARCH(O11,$C$5:$C$19)),--ISNUMBER(MATCH($D$5:$D$19,{"One","Two","Three"},0)))
The issue is that I need where {"One","Two","Three"} is to contain between 3 and 334 conditions.
Basically we have a data table that contains "channels" that have multiple affiliates under it. Then we have a data table by affiliate by country and revenue... I want revenue by Country By Channel...
I have this vb script which basically states if a value in column G is less than 0 then show a message box as below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
End If
End Sub
What I would like to do is extend this script so that another message box pops up based on two conditions
i.e. if a cell in column F shows string "accounts use only" AND value in the adjacent cell in column G is > 0 then MsgBox "you have entered a positive figure - are you sure this is correct?"
I'm trying to adjust my macro to fill the interior cell color of a row if there's a non-blank row above and below it (i.e. if there are 3 or more non-blank rows together, then fill the 2nd, 4th, etc. rows. If there are only 1 or 2 rows together, then don't fill the cells.
However, what I'm getting is filling ever other row, with this code. There seems to be an error in how I'm trying to use and If condition with 3 criteria.
Code:
Sub colorin()
Dim LastCol As Long
Dim r As Integer
r = 6
With ActiveSheet
LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
[Code] ....
Here are some sample results (pretend cells with red text are actually cells with interior color, black text is an unfilled cell).
Rank
Name
1
Jones, Some
[Code] ......
What I'm trying to achieve is: (again, red text actually represents filled cells - can't get sample shot of actual filled cells to copy into forum post).
Rank
Name
1
Jones, Some
[Code] ...........
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course.
The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View 4 Replies View Relatedi am not able to solve this problem.
1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table
2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month.
3- Reference table has the list of the same data record ( numbers ) .
Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following.
a- Check for the record in the reference table , if doesnt exist then add the record in the reference table
b- create another sheet same as the sheet " A" (duplicate of
A) and name it the new record number
b- Plug the new record number in the cell B2 of the new sheet
c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to
O8)from sheet A
I created a master data sheet to enter data manually to analyze. It contains 20 columns, and rows(continue change, because we enter data all of the days). I need to extract data to another sheet, when the user enters data in the master data sheet, but with some conditions, when the column 6 contain the word ASQ, and then extract all of the data, but the new sheet do not contain 20 columns, because the data that contains the word ASQ have only 16 columns.
View 6 Replies View RelatedI need to create a macro to extract data from sheet1 to sheet2.
The sheet1 contains 15 columns, and the data starts at row 5.
The sheet2 contains 15 columns, and the data starts at row 5.
We enter data all of the days, so we have like 500 rows in the sheet1.
Only 2 conditions to extract data from sheet1 to sheet2:
if the column9 of the sheet1 contains the word "NO" and column11 of the sheet1 contains the word "ASQ".
I am creating a document log that tracks all excel files sent and received.
I use RDBmerge to get the filenames and data from the file batches.
My current macro edits the data down to the last stage of data needed to create the log.
My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"
Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.
I have highlight the cell range on the "Final_Data" Sheet that I need to complete.
filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.
The criteria is this:
A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")
I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.
i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method
[Code] .....
I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.
I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.
VB:
Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range
[Code]....
Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''
I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".
View 1 Replies View RelatedCurrent simplified situation:
Department A
Employee_1
Employee_2
Employee_3
(empty cell)
Department B
Employee_4
Employee_5
Department C
Employee_6
Employee_7
Employee_8
Employee_9
Employee_10
With the VLOOKUP function, I have looked up the row numbers of the departments. Subsequently, I'd like to find the last employee of that department, based on the department's name and/or the department's row number.
A department-typed cell is non-empty and never contains an underscore. An employee-typed cell is non-empty and it always contains an underscore.
The simple and non-sufficing formula is the VLOOKUP of the row number of next department minus one. But with adding, moving and subtracting departments rapidly, this is not an option.
I'm using 2007 but I need this to work with Older versions so I tried to combine the condition for red
View 14 Replies View Relatedfind a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received.
I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.
Here is my formula that works so far.
=SUMIF(Data!C:C,"Office",Data!D:D)
Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM
I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.
=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")
Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
I am trying to do some conditional formatting to highlight rows based on several conditions, I don't know if this is possible, but these are the criteria:
AAPL
tt
10/1/2013 11:14:15
GOOG
ll
10/1/2013 11:14:20
[code].....
I want to highlight a row if:
- the difference in time value is < 20 seconds
- the first symbol column is the same
- at least one, but not all of the rows meeting the above criteria contains a value in the second column from a given list (in this example, say tt is on this list)
So if done properly the logic would highlight the first and third rows, but would highlight none if none of the values in column two match my list.
I think the test criteria would go something like this, however I know that there are problems with my row references.
=and(abs($C2-$C3:$C65536)<0.0003,$A2=$A3,match($A2,Sheet1!A:A,0)>0)
I want to highlight A1 red if C1 is greater than 55. Is this possible?
View 3 Replies View Related