The left column is the product, the right the delivery day.
I need to Check and extract each product delivery days. For example, for Q-betacicl I should:
1.- in cell f1, use a Vlookup function to extract 22-jun. thatīs done.
2.- in cell f2, use an If function that compares the cell below Q-betacicl with the cell below. If itīs 0 (like this case) it should extract 01-jul
I used this and excel didnīt even let put the formula, arguing some error I caīt find:
Look for a value equal to E6 (q-betacicl) in matrix A5:b27, and if the cell immediately below it = 0, copy the value to the right of that cell (the one below it).
In the case I showed, for Q-BETACICL, it should copy 01-jul. For N-CONTROX it should copy 02-jul.
In cell M32 there will either be an O or the cell will be blank. In cell T51 I had placed an IF statment like this:
=IF(M32="O","","X")
Now that works ok but there is a glitch on my part. If T51 is blank because of M32 containing an "O" then the user will also enter a number into T51. Because I was using an IF formula in the cell it gets overwritten because of the user needing to enter data in the cell because of M32 containing a O. How do I write a macro so that a similar formula is used but still able to enter data manually without overwritting the formula?
My program assigns a variable one of three values - either "Lancaster", "DeSoto" or "Cedar Hill". My string compares for Lancaster and DeSoto work fine but can't get anything happening for Cedar Hill.
I looked at the value of the variable and it appears to be correct. However, when I did a StrComp for the variable and the string "Cedar Hill", they did, of course, not come out as equal.
I am currently trying to take a list of people who have log in times at work however if they log out then back in the result is what you see below.
What I am looking to do is match the find the name then find the earliest time (login Column C) and then the last or latest time (log out Column D)
A B C D John Smith64676076:02 AM6:13 AM John Smith64676077:25 AM11:27 AM John Smith64676212:08 PM4:01 PM John Smith64676076:13 AM7:25 AM
Would like a formula as I have another Tab on the sheet that looks up name and compares against a schedule to see if the person was late or left early.
Is there a Function or compound Formula that compares the values of two ranges and returns True/False ?
Something along the lines of : AreRangesIdentical(Range1,Range2) returning True/False.
I have played with some array formulas but they seem to compare the two first cells only.
I know one can easily come up with a UDF that loops through each cell and returns once a cell is found whose counterpart is different but i am looking for a solution that doesn't use a loop.
I've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.
I would like to build a graph that compares 2 sets of data but only plots 1 dot where the two intersect. I have seen this graph made by others in Excel but I do not know what it is called or how to make it. An example data set is below.
ID Age Height (cm) 1 5 125 2 7 140 3 8 138 4 11 152
I would just like to plot the location where the age and height for the ID's cross and not 4 plots with the age and 4 plots with the height.
I have a workbook that compares two sheets of data by taking the difference between the two. Some of the cells contain N/A for value, so when I take the difference of two sheets, it gives me #Value! error. My question is how can I format it, so when the cell contains text it displays N/A or if its a value: it takes the difference and populates the value.
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
I have a spreadsheet where a column has many cells being empty and others with values. I need to use copy-paste skip blanks to another column so it only overwrites cells that contains values. BUT The cells in the column appears to be empty, not blank, when I try use the copy-paste skip blanks it doesnt work. However, when I press delete in every empty cell the copy-paste skip blanks works for those cells.
Do you got a fast method to make all the empty cells blank?
I have a spreadsheet for which I have to set up a formula to get the minimum value from a range of cells, but that range can include blank cells, errors (#DIV/0) and zeros, all of which I want to be ignored. I can work out how to ignore EITHER the zeros
(=MIN(IF(C10:G100,C10:G10)),
or the error cells
(=MIN(IF(ISNUMBER(C9:G9),C9:G9)),
How to exclude both. If I try to combine both of these exclusion criteria it doesn't work and I end up with the answer #DIV/0, which is one of the values I want it to ignore.
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
I have 25 cells, that can either have a value of 0 or o.2 The cells have to be added up. No problem there ofcourse. But if a cell has a value of 0, -0.25 should be subtracted from the total.
I have this formula, but I'm not sure how to use it for multiple cells:
Cells in column B need to be colored red if they are equal to 50% of the cells in column C in the same row, or if the cell in column C in the same row is 100.
I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).
Is there a way to add the round function to the current formula in multiple cells at once? I have a worksheet that has many cells that are linked to another worksheet or workbook, and I need all of the cells to be rounded to 2 digits. Can I do this with a macro?
How this code is not working correctly. I want to count the number of cells with constants that are in sheet 2 column B and return this number to cell A3 in sheet 1 if and only if A2=A1 in sheet 1
Code: Private Sub Test() Dim x As Single, Tprim As Single, Tprim_ref As Single, Counter As Single Tprim_ref = Cells(1, 1)
I am trying to make a function that will only show the value of visible cells (so I can have excel calculate a slope/offset of a filtered table. I made the function below, and it seems to work pretty well.
Option Explicit
Function Visible(x) As Variant Application.Volatile If x.Rows.Hidden Then Visible = "" ElseIf x.Columns.Hidden Then Visible = "" Else: Visible = x.Value End If End Function
However, when I try to use it in an array formula instead of with an individual cell (example, {=SUM(Visible(A1:A10))} ), it only evaluates the hidden property on the first cell. So if the first cell is hidden, all cells will be blank, and if the first cell is not hidden, all values are summed, regardless if some of the later cells are hidden.
I usually use the IF and IFERROR functions with the SUM functionto filter out values I don't want, and I assumed I could do the same with this custom function.
I've been looking for a while on here now and have found some great formulas, but can't seem to get this 100% figured out. Here is what I want to do. My master sheet contains a cell (A5) that is used as a keyword search. So if I wanted to find info using my name I would enter gsheppar in A5. I have another sheet labeled "List" where there are different categories i.e. A1 is labeled Online Retailers, A2:A51 contains a list of the retailers. In B2:B51 I would enter my name (gsheppar) if I had an association with the retailer.
On the master sheet when I enter my name I would like it to show all retailers in B5:B55 that has my name next to it in B2:B51 from the "List" sheet. The problem I am running into for example B2 on the list sheet has my name plus 3 other people's names formatted: gsheppar,name 2, name 3. The question I have is how to have the formula in B2:B51 on the master sheet look specifically for the name that is in the keyword search when there are multiple names in a single cell on the "list" sheet.
I'm trying to count the number of times a pupil gets a detention which has been rescheduled.
In the main worksheet below I want to be able to count instances like the one on row 11 where John Smith has had a rescheduled detention.
I want to be able to connect this rescheduled detention to John Smith on a different worksheet in the same workbook. On the screenshot below I would like to have Column D as a running total of rescheduled detentions.
I have tried all sorts of permutations of the COUNTIF function but without success.
which is meant to return the number 1 in a cell if C38 has the letter C in it and F38 is equal or greater than 0. I have met both criteria and it returns an error of #VALUE! What am i doing wrong? NB in F38 there is a Vlookup formula that returns a number (in this case it is 0).
If cell D3 has the text string "RQS" and cell D2 is colored green, I want to count as one. The formula needs to apply to a range of cells. The end result being I want to know how many green cells are delineated at RQS.
If have very simple =IF function, =IF(F4=H4;1;0) but don't wan't the 'value is true' value when both cells are empty, how do I need to change the function so it states 0?
Is there a way to plot a function, for example y = a + x*b or y = e^x in a graph without it having x and y values in cells of the spreadsheet?
That is, in y = a + x*b, for example, what I'd like to do is have an input cell for a and b, but no cells that show a value of y for every corresponding value of x , and a graph on a chart showing what the function looks like. Thus, the graph would only rely on inputs of a and b and on nothing else. A program called MathCAD does this, but I'm not sure how to do this in excel.
