I have simplified my problem to its basic parts:
Column A Contains Customer Numbers,
Column B Contains a Balance.
Customers can appear more than once, but the data is sorted by customer number.
In column C, I need to put a 1 when the balance reaches 0 or less, but only for the first instance. Everything else should be 0.
here is how i'd want it to look:
customer
Balance
1st
I have a few dozen columns of data and would like from each row to count the number of negative numbers in every 3 occurrences. E.g. in row 2 I’d want to count the negative occurences in B2, E2, H2, K2 etc. Also, I’d like to be able to count the total number of occurences along each row, again counting every 3rd occurrence, though this time both negative and positive values.
View 4 Replies View RelatedI have a medication start end and admission date.
and i want to assign a number value of 1 to all the records that the medication start date is 2 days after the admission date. How do I do this using the ifs function.
I have been given a spreadsheet with the sales data for each of our salesmen. Each job has its state listed and what I need to do is break down the information by region. The catch is that the regions are not given - just the states. So I need to assign the states to their corresponding regions. To do this, I tried writing an "IF" statement like this:
=IF((B2="AL"),"SOUTHEAST",IF((B2="CA"),"WEST",IF((B2="NJ"),"NORTHEAST",IF((B2="TX"),"SOUTH CENTRAL", ... and so on.
So I was doing this for all 50 states. The problem is that the statement got too long. So how do I write this? There has to be a shorter/easier way.
Trying to assign this cell's value to the correct sheet tab for the current month.
Trying this:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0),IF(MONTH(A3)=6,'[Wholesales 2014.xlsx]Jun'!$P$1,0)
A3 is todays date. Wholesales 2014.xlsx]May is a seprate sheet for the month of may and so forth for june.
The problem is the p1 value is empty for the Jun sheet so I get an error: #VALUE! (I assume that is why I am getting the error?)
Using:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0)
By itself works just fine. I need to set this up for future use, though.
Rows:
Each row contains a new client (a new entry)
Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)
A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.
Etc...
I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.
The problem i am having is that i need a way to have a two-condition statement as such:
if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................
I have a problem with a SUMIF statement. I think it is =SUMIF(AND(...
I have attached I want to sum in column E lines 255-316 if in cell D 34 and if cell B25 equals GL and cell C255 equals 0001.
Maybe this would be handled better with a sumproduct formula...
I'm having a problem with negative values. Somehow if the pitchX is -11.1877, let's say, and it is the right value, the code will still perform the 2nd if statement, when it should not. Am I using the wrong data type? Or is there anything I'm missing out?
Dim pitchX As Double
If pitchX > 10000 Then
pitchX = pitchX / 1000
End If
If pitchX <> Range("C8").Value Then
With Range("C8")
.Font.Bold = True
End With
End If
=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year
=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date
I am using this condition to calculate the length of a service which falls with in a specific review period, eg
Service starts 1 Feb 09 and finishes 30 April 09
The review period is 1 Mar 09 and finishes 31 May 09.
I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09
At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.
I am attempting to create a multi condition IF statement for work. Essentially what we have is a column dedicated to the date in which a piece of equipment is supposed to go into service.
What we want to do is make it so that if the current date is 40 days past the scheduled in service date, a status column displays the term "Verify".
Additionally, if the scheduled in service date is 365 days from the current day, a status column should display the term "Future".
If it is any day in between the two, it should display the term "Active"
I have two seperate lines of code, one that covers verify and active and the other that covers future and active but I can't combine the two so that I have one function. The code I compiled is included below, any help would be appreciated.
=IF(D7=0,"Active", IF((TODAY()-40)-D7>0,"Verify","Active"))
=IF(D8=0,"Active", IF((TODAY()+365)-D8<0,"Future","Active"))
(D# corresponds to the respective cell that contains the scheduled ISD)
I am running a macro which filters a column to show only records with "#N/A" (ie an error), and copies these to another worksheet. As I am aiming for no errors, there will be occasions when there is no filtered data.
My problem is that, unless I use something like xlCellTypeVisible, when there is no data shown it still copies and pastes all the hidden records. And if I do use it when no filtered data, I currently get an error "No cells were found".
I have tried many iterations of code correction and i think i'm currently blinded by staring at it so long!
My current if statement reads as follows:
[Code] .....
I think I might need to use some sort of On Error Resume Next statement, but I have never used these before. When I tried adding that before the If statement, it just ignored the criteria and tried copying hidden rows - the exact opposite of what I want.
I'd like something that said, if this condition = error, skip over entire if statement and carry on with rest of the sub.
I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.
What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.
So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.
SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))
I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
I would like to lookup the last occurence of a particular cell value in a worksheet and use the cell value in the column immediately adjacent to this one in a calculation.
For example, The text "Run #" occurs several times in a sheet with a numerical value in the next column over. I would like to use the last occurence - or as far as that goes, the largest occurence of the numerical value. Unfortunately, there is additional data in between the "Run #" occurences, so a simple max() for the column will not work.
I am trying to extract the last occurence of ppp (4th column). So as long as 4th column is "ppp", i would want to extract the row wher the the last "ppp" occurence start and display in sheet2 "108-Jan-07vcdppp4". If data in 4th column is detected to be "ppp1", it would also extract the row wher the last "ppp1" occurence start and display in sheet2.
Note that the number of ocurrence is random and do not follow specific pattern. Pls see the attached for the example.
I have two sheets:
Sheet 1
Col A
A
B
C
D
Sheet 2
Col ACol B
AXX
BYY
AXX
AZ
DD
DD
I want to count the number of unique occurance of value in Col B in Sheet 2 as per value in Col A in sheet 1 and Sheet2.
My output result should be like following:
Result
Col ACount of col B value in Sheet 2
A2
B1
C 0
D1
I need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.
I have tried using array formula
{=MATCH(E1,IF(A2:A21>=E2,IF(A2:A21=E2,IF(A2:A21
I am trying to find a way to return an second answer for a second occurance using a VLOOKUP function. (maybe it can be done with something else?)
ex. range= A1:C3,
A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER
VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"
How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?
[data] ....
I'm having a problem seperating the prices in Colomn H into three seperate cells C,E & G
The first figure in c is easy enough if a little long (any possible tidier solutions?)
The second Im not happy with it is clumsy using the fixed lenght figure "5"
The last I cant figure out probably because Ive done the second incorrectly!
Finally I can not see why figures apear in away team.
I have tried using the 'arbitrary lookup' function as listed here but can't seem to get it to work. [URL] .....
I have a list of all football fixtures and results in the premier league this season and I have a separate worksheet with a drop down menu to load each team.
What I am struggling to do is to get a function that will obtain the data corresponding to each of the home fixtures and the away fixtures of the team selected in the drop down menu.
I have managed to get the first result but do not know how to get the 2nd occurrence, the 3rd etc.
My formula for the first is
=INDEX(Fixtures!$A$3:$R$380,MATCH($E$1,Fixtures!$C$3:$C$380, FALSE),5).
I've attached the spreadsheet. (Unfortunately the format of the excel sheet can't be changed)
Forum Example.xls
I have attached the excel sheet for reference. As shown in the "After" sheet, i need the 2nd to nth instance of duplicate keywords to be highlighted with some color.
View 9 Replies View Related[URL]
Now what I need is a formula similar to the one provided in the above referenced thread; however, I need it to sum the totals of all Children (C) from column BW in the attached excel file, and return the sum values found in BW to the corresponding employee (E), before the next E occurrence, or blank, if the row below the last row containing a C value in row M in blank.
refer to the excel file, specifically the row highlighted in RED, and the explanation to the right of it, for further clarification on what I'm needing the formula to do.
Ultimately, I have a census of employees with rates, and some employees cover their children, and I'm needing a formula that will be able to total up the monthly premium for each employee's covered children, and bring that total up to the same row that the employee's data is located.
I have a column of data that contains a two letter identifier, which can sometimes be combined with another 2 letter indentifier: Example
Column D
AA
DC
DC,AA
How would I get countifs to count each occurrence of AA or DC by itself?
I am trying to find an in-cell formula to find a cell with a specific value and return the value in column A of the same row. Typically this is handled by Lookup or VLookup or HLookup. However, these only find the first occurrence of the value, I even know how to find the last occurrence, but what about in between? In my spreadsheet I have reoccurring values that I need the value in column A of the same row, but I need more than just the first or last but every instance in between. How to do this?
View 4 Replies View Related