as an example, i have a column of data that includes cells with blank and non-blank values...i want to write a formula that will refer to this column and list the non-blank values - either in one cell or separate cells.
View 9 RepliesI have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.
View 7 Replies View Related=IF(Q20+R20+S20>0,Q20+R20+S20,"")
V20
=SUM(T20*O20)
V20 gives me #VALUE
How can I have V20 blank if T21 is blank?
I have a column of imported data like this, with scattered blank cells:
Rd Frnt200634350464300399636350
I am doing a multiple regression where the above are independent variables. I usually have hundreds of variables in a column like this. Because each cell must have a value in it (the regression won't accept blanks), rather than manually fill the blank cells with a 0, I set up an adjacent column to convert the blanks to a 0 and regress off that column. But every formula I try still produces a blank in that adjacent column. I have tried things like: =IF(A1="",0,A1) but they do not work. I always get a blank in the adjacent column when the original cell is blank. Can't get a 0 in there.
How do I have this formula return a blank space if no match is found?
=INDEX($B14:$G14,MATCH(O$1,$B14:$G14,0))
i have IF sentence that returns either a number or a blank cell (tried using "") in a colum. This data is entered in a chart.
My problem is that that excel dont recognise the "" as a blank cell in my chart.
Is there a way to do this? i could maybe use VBA to make my own function that clear.contents if the cell value is smaler than say 1?
I am trying to fix the below formula =IF(OR(ISERROR((F26-E26)/F26),((F26-E26)/F26)=1),"",(F26-E26)/F26). If I get an error from the formula I want it to return a blank cell. If I get 1 as the answer to the formula I'd like it to return a blank cell. This formula only works if the result is 1 but won't return a blank cell if the answer is an error.
View 2 Replies View Relatedfind attached my spreadsheet. I want the excel to look at j2.. search for it in A coloumn and return its corresponding B coloumn values. There might me multiple values with the same name in A2. I would like the excel to return all the multiple values with their corresponding B coloumn values.
View 3 Replies View RelatedEach row in Table 1 represents an "Expanded" array formula.
In Table 2 I have a "Running" formula to return progressive max values working Left to right.
My question is, can this be written as an array?
Returning something on these lines ...
{(IF(B1="","",MAX($B1:B1))),(IF(C1="","",MAX($B1:C1))),(IF(D1="","",MAX($B1:D1))),(IF(E1="","",MAX($B1:E1))), etc.}
Drag B7 across and down to see what the array should contain for each row in Table 1
Sheet1 ABCDEFGHIJKLMN1Table
1 1234 42 141 5 3 53 1 46 423 64 122 4567 89105 122 4657 89106 7Table
2 1234 48 144 5 5 59 1 46 666 610 122 4567 891011 122 4667 8910Spreadsheet
FormulasCellFormulaB7=IF(B1="","",MAX($B1:B1))
I've got a complicated logic formula which looks at multiple values on a large spreadsheet to return a singular figure based on whether all these conditions are met. It works well but I wan't to add an extra stipulation to it. I won't paste the full formula as it probably wont mean much without all the data so heres a mini version with simplifed conditions - but that DOESNT work:
=SUMIF(K10:K13,IF(H10="CAT","CAT",IF(H10="DOG","DOG",IF(H10="ALL",OR("CAT","DOG"),""))),L10:L13)
Its the last bit that I'm having problems with - I want to say if H10 equals "ALL" then in my table of values to add up not just "DOG" values or "CAT" values but "DOG" and "CAT" values
In range B4:M4 some cells contain values and some are blank. I am trying to write a formula that will omit the blank values and just give me the nonblank values. The code below returns the FIRST nonblank value, but I can't seem to figure out how to drag it across a row to get the others. I want to ensure that the numbers stay in the same order (so no largest/smallest formulas), but the blank cell values need to be removed.
This would then need to be repeated for (B5:M5, B6:M6, ETC).
[Code] .....
Ive got a chart that dynamically in/decreases its range. Area that does not contain any data (end of the chart) however contains formulas displayin NA value in order for the graph to not display zeros (its a must since the chart get data from dif source and when there is no data in the source then blank is displayed). Nevertheless, NA values are not displayed which is OK, but the chart still remains longer (with blank data at the end). How to prevent it from completely ignoring the NAs?
View 5 Replies View RelatedI am designing a spreadsheet for my colleagues and I have run into a problem. Range U16 tu U and the first blank row has values TRUE or FALSE. The problem is you will never know if it's U16:U21 or U16:U90. The thing is to come up with a formula (no vba) that will count all TRUE values in that range. I found a formula on the internet to find the first blank column: =MATCH(TRUE,INDEX(ISBLANK(U16:U300),0,0),0) + 16 - 1
The formula works great and if I put it in, say, cell P2 it will return the correct result.
Now, the cell K2 has the following formula: =COUNTIF(U16:U300,TRUE). It Counts incorrectly. I do not want it to run to U300 but to whatever P2 returns. How can I do this?
I am trying to use a formula similar to VLOOKUP() to look in a table for two values and if both values are found, return another value.
Sheet2 is an example of my output file, sheet one is a generated file. i would like the vlookup formula in sheet 2 cell D2 and down to be something like this,
=VLOOKUP(A2,Sheet1!A:K,10)
But I would like to lookup A2 and B2, and the row that contains both values, return the value in the J column
I'm trying to use vlookup to return multiple values for the same unique identifier. I've read a couple of examples within the forums, but I haven't been able to find anything I understand. I have employee ID's and these ID's have specific access associated with their ID's and each access is listed within a different row. Within a new spreadsheet I removed all duplicates of the employee ID so now I no longer have a one to one match between spreadsheets, so I would like to create a vlookup that will list all access associated with each employee ID. I have attached a sample of the data sheet I am working with.
View 5 Replies View RelatedI am trying to create a formula that will do the following:
If A1 is between 95 and 99 then populate with 2
If A1 is between 90 and 94 then populate with 3
If A1 is between 85 and 89 then populate with 4
If A1 is between 80 and 84 then populate with 5
If A1 is below 80 then populate with 6
I basically have this worksheet which has Cell D3 Being Dynamic. It puts the lowest cost supplier from columns H:W. If i change the prices in H:W it will bring up the lowest cost supplier in D3. Once D3 is chosen i want it to self populate F3 and G3 based off of what is in D3. This data should be pulled off from what is in the H:W columnns corresponding to the supplier in D3.
View 3 Replies View RelatedColumn A:
pants A green
Pants B red
Jacket C green
Jacket D blue
Polo C white
....
I need a formula to find some value( in our case "colors" = "green, red, blue, white" ... = 20-30 values), and to return in column B, the values, like:
Column A: Column B:
pants A green | green
Pants B red | red
Modern green Jacket | green
blue winter jacket | blue
Polo white D | white
I have different product feeds (csv) and i will like to search for colors in the product names.
I am trying to find a way to use information in one cell in order to look up corresponding multiple values on another sheet. The problem that I am running into is that VLOOKUP only returns the first value. I need the values to be calculated in different cells going vertically, not all combined in the same cell as was in an earlier post. I am not looking to sum anything so a pivot table also doesnt work.
Sample data:
A / B / C / D
Identifier / Pub ID / Invoice # / Job #
ABCD1234 / ABCD / 1234 / A41254
ABCD1234 / ABCD / 1234 / B41254
ABCD1234 / ABCD / 1234 / C41254
DCBA4321 / DCBA / 4321 / A56789
etc.
I am looking for it to do this:
A / B
Identifier / Job #
ABCD1234 / A41254
/ B41254
ETC.
I want to enter ABCD1234 into another worksheet and have it return all of the job #'s, but I have a lot of these so I don't want to have to look up how many job numbers there are associated with it and have to enter different formulas into multiple cells. Filtering also isn't an option as there are simply too many cells to calculate.
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies View RelatedI have number data in columns F through AK. In column AL I want a formula that will look in columns F through AK from the left to right. Once it identifies a positive value I want the formula to identify if after that positive number there are any occurances where there are 6 0's in a row (anywhere up until column AK). The formula can return a True or False. I want to drag the formula down across many rows of data. If the entire row contains 0's, I do not want it identified as a TRUE
View 9 Replies View RelatedI have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.
View 8 Replies View RelatedI can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View 2 Replies View Relatedin one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies View Relatedtable1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedUsing one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View Related