I'm trying to write a formula that would filter data from one column based on condition entered in a cell. This one works great with single numbers or text in a reference cell:
=IF((a2=C$3);a2;"") copied down the column
but if I try to filter all numbers greater than 2 (I eneter ">"&2 in cell c3) it doesnt work. Is it possible with if formula or should I use something else?
Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedI have a worksheet where two columns of data are written. In another worksheet I have a list of data in one column. In column next to it (so the rows are the same) I am trying to write a list of sumif formula, where the range is first column of the first worksheet, condition is a value of the cell next to where I am writing the sumif equation (list of data) and the sumrange is the second colummn in the first worksheet. I can't get it. I tried with --- "C" & i --- but I get an error saying expected:end of statement. It goes like that:
View 2 Replies View RelatedI am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.
Issue: All I would like to do is for a NESTED IF Function to be able to say this:
1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square
2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.
3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.
If 'Column A' - 4" Square, Then 'Column B' - A
If 'Column A' - 5" Square, Then 'Column B' - B
& So on...
But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:
User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A
User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B
& So on...
Currently to do the above function, I have utilised the following Excel NESTED IF Function:
[Code] .....
& The following happens:
1. User inputs data into 'Column A'
2. The NESTED IF Function then provides an automatic response.
3. However it relies on the data being inputted without the ' " (Quotation Symbol)'
4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"
Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.
Example - IF Function.xlsx
something that looks like =CELL.MIN(reference)
View 3 Replies View RelatedI have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.
in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.
So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3
I have a column of numbers, on which i would like to perform subtraction, in a way that the cell in the (i)th row will be subtracted from the one in the (i+x)th row, while x is a parameter that the user can change as he wishes.
My problem is how to refer to the cell address and still use this conditional reference...
If anyone has an idea how to realize this calculation (which should be peanuts in Matlab, but apparently more problematic in Excel),
is there a formula that would have as it's output the cell address of the
same cell where the formula has been entered.
e.g. In cell A10 I input a formula whose output is the cell reference A10.
i have two excel files, with many data inside
i need a function that do this:
in the cell A1 i want to visualize the cell A1 of second file
in the cell A2 i want to visualize the cell A13 of second file
in the cell A3 i want to visualize the cell A25 of second file
i explain: in the first file i need a column that in each line show the first cell + 12, then +24, then +36.
file 1 is 1.xls
file 2 is 2.xls
for example A1 have the value 1
if in 1.xls cell A1 I insert =[2.xls]worksheet1!A1 and in A2 =[2.xls]worksheet1!A1+12 in A2 i obtain the value of A1+12=13, but i need to obtain the valour of A1+12=value of A13.thanks
I have a problem using COUNTIF function. I want to count cells say in range A1:A10 that are smaller that a value in a cell let's say B1. Neither of these works:
=COUNTIF(A1:A10; "<B1")
=COUNTIF(A1:A10; <B1)
How to do it? I saw your answer of Dave to a similar problem, but I still do not understand it.......
I am trying to acccess a cell value from a seperate worksheet. The cell reference needs to be generated by a seperate formula. In this example
=DSInfo!C28 // provides the correct result i.e the contents of cell C28 on worsheet DSInfo =MATCH(A4,DSInfo!C1:C35,0) //provides the correct row number - in this case 28
However on trying to combine the two =DSInfo!C&MATCH(A4,DSInfo!C1:C35,0) // provides only a formula error. I'm guessing this is a simple syntax error on my part but after hours of trying various ways I'm still having no luck.
Is there a way to start match from a relative position?
Say a match I have in column "A" returns 344. Is it possible to start a new match for column "B" from that spot, instead of having it return a match starting at the top?
I will show with an example of what I would like to do. I italized and underscored the part of the formula.
---A----B
1 24 formulaB
formulaB = "= CELL("contents",DifferentSheet!G(A1))"
I want B1 to contain the value from the cell G24 in sheet "DifferentSheet". I want to use the value in A1 to determine what row on "DifferentSheet" I should use.
how can I use the cell function inside a NPV fuction as one argument of the start for the NPV string to calculate?
View 3 Replies View RelatedI have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.
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.
Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.
In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number
THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.
There is a function sumif() that is used for do sum() under condition
Nevertheless, if I want to do max() under condition, how can I do?
There isn't a function maxif().
I need to use the rank function to get the position of each item in a list based on its corresponding Revenue. However, the range that has the revenue numbers also contains a percentage related to that item. Basically, I need to use RANK based on a list that ignores any elements which are <= 1.
View 9 Replies View RelatedBelow is my function.
="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))
I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.
i tried to compare 2 different cells with text inside.Each cell contains "yes","no"," ", 6 possible values
For example
A B C
1 YES NO
2 YES YES
3 YES " "
4 NO YES
5 NO NO
6 NO " "
In column C, i want to enter the result f.e. if a1="yes" and b1="yes" do 1,if a2="yes" and b2="no" do 2 and so on There is a function for this?
we can get if functions to return conditions, as in if so and so "a" etc... is it possible to return long statements, instead of letters, with fill stops and commas. i don't know if it would be appropriate if i put an example of what i was trying to do. i am not trying to be rude but this
will this work in c3
=if(b3=5,"Readily understands classroom statements, questions and instructions.Can usually understand short electronic texts without difficulty.",if(b3=4,"Understands most classroom statements, questions, instructions.Can usually understand short electronic texts with only occasional difficulty.",if(b3=3,"Can generally understand classroom statements, questions and instructions, but may need repetition.Can understand short electronic texts reasonably well.",if(b3=2,"Sometimes misunderstands classroom statements, questions, instructions.Has some difficulty in understanding short electronic texts.",if(b3=1,"Has noticeable difficulty in understanding classroom statements, questions, instructions.Has great difficulty in understanding short electronic texts.")))))
I have huge set of data listing almost 20 columns and 400 rows..i am trying to apply a formula for one of them..
Condition: Onshore or Offshore
Data to be retrieved: list of locations at onshore and offshore
Result expected: If a particular cell is selected as onshore then allow user to select only Onsite locations same for offshore
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
View 5 Replies View RelatedHow do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I have a the following function: =INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))
I'm trying to put the get the value of it from a different Sheet.
say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change? I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)...
nothing works.
what the function does? there are 2 tables. table 1 has values and table 2 has values as well. K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1. for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344
I need a function where the sheet-reference can be linked to a cell.
By other words can the content of cell A1 be sheet1, sheet2, sheet3 etc. The cell in B2 shall always point to cell b3 in the choosen sheet, but the sheetreference is dependent on the sheet refererred to in A1
i got three cells namely; M7, AC7 and BA7. Initially, an input value is entered in cell M7 which will be processed to give AC7. An IF function is entered in cell BA7. This IF function is really simple. It states that IF the difference between the values of M7 and AC7 is greater than 1 then, decrease 0.01 from M7, otherwise, retain the value of M7. The syntax is:
=IF((M7-AC7)>1, M7-0.01, M7)
As I told you, it is really simple and looks fine. I have no problem with it so far. The problem came about when I tried to make the circular reference. I am trying to make M7 to equal BA7. Well, before I made the circular reference, I made a manual entering of the values of cell M7. What I have done was that I put a value like 29.94 in M7 then I let the calculation to proceed. The the value that I get in BA7 (i.e. 29.93), I re-entered it in M7 then I repeated the process until I got a value (i.e. 29.90) that was not changing due to the use of the IF function. The manual calculations are shown in Table2 of the attached file.
I said to myself instead of entering the values manually, I can use a circular reference so that the cell M7 = cell BA7. Unfortunatelly, the circulation of the values instead of giving me 29.90 which is the right answer, it gave me 29.89 which ment that Excel has done one more step further of the IF function. In other words, Excel should have stopped decreasing 0.01 from M7 when M7-AC7 was less than one. This is achieved at M7=29.90 (as clearly seen in Table2). However, Excel went one step further and done: BA7=29.90-0.01=29.89 then it stopped subtraction at 29.89 which is not correct.
Each sheet contains the test data for an individual class and I am trying to create a "Totals" sheet where I can show the average that each class had on a particular test and the letter grade breakdown. I am trying to create one 'average' formula that I may then copy across that will change the sheet reference automatically without me having to manually re-create the formula every time I add a new class sheet. I tried using the INDIRECT function but could not get it to work. Keep getting #Ref error.
View 3 Replies View RelatedIs there any way to use a formula rather than a direct cell reference in the Reference argument of an Offset function?
For example, rather than this:
=OFFSET(H3,0,-7,1,1)
I want to do this:
=OFFSET(max(H:H),0,-7,1,1)
Effectively replacing the starting cell of the offset function with a dynamic formula that returns the maximum value of an array.
