IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)

Dec 26, 2008

I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.

I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?

It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...

The first step i have taken are:

1) if the 2 date ranges are the same to say 'true' if not 'false'

I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date

I am trying to come up with a simple formula to count a single cell if it contains either a 2 or 3.

The cell can contain numbers ranging from 0-8.

Even better would be if I could some how evaluate single cells based on the contents and then count the number of cells where the criteria for contents is true. The problem is the criteria differs from cell to cell (i.e. D2 could = 2 or 3, but E2 needs to be counted only if it contains a 4 or 5).

I know COUNTIFS only evaluate a range but it would be perfect if I could somehow get it to work for single cells. COUNTIFS(D2,2,D2,3,E2,3,E2,4,F2,2)

Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.

In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?

We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum ....

I´m trying to do a macro that changes the color of all fonts in a row if the cell in the column "R" has the date lower than a specific one, something like this:

Dim Data2 As Date Data2 = Sheets("Sheet1").Range("today") Range("R12").Select Do While ActiveCell <> "" If ActiveCell < Data2 Then ActiveCell.EntireRow.Select Selection.Font.ColorIndex = 3 Else ActiveCell.Offset(1, 0).Activate End If Loop

But it just don´t work. The macro does that in the first row, than it stops. Do you have another code for this, or another way to do?

In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.

Capture.PNG

At the moment I have a formula as below:

=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))

this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.

Modifying my formula. I have attached the sample workbook below.

I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A. The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?

Sub Recalculate() Dim r As Integer r = ActiveSheet.UsedRange.Rows.Count

I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")

This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?

Rather than having to give it a range, I'd like to have a generic range giving me room to add or subtract values in the column without changing this statement.

Sheet1 *BCDEFG3QAUSERQAUSERQAQA4SRF RegistrationWorkflow-UserWorkflow-QAWorkflow-User FeedbackORMC 1ORMC 25CompletedCompletedCompletedCompletedCompletedPending Excel tables to the web >> Excel Jeanie HTML 4

I need to create a formula that would read from cell B5 to G5 and the first "Pending" it finds, it should populate the corresponding value from B3 to G3.

In this case, since from B5 to G5, the first "Pending" is in cell G5, the formula should return as "QA".

Sheet2 *BCDEFGHIJKL2Parameters**********3Last Done**********4Market Cap (M)**********5PE**********6Price/NAV**********7High, 52 Wks**********8Low, 52 Wks**********9Revenue Growth**********10Current Ratio**********11Debt to Equity**********12ROA**********13***********14StockLast DoneMarket Cap (M)PEPrice/NAVHigh, 52 WksLow, 52 WksRevenue GrowthCurrent RatioDebt to EquityROA15***********

1)I need to create a formula to populate all stocks that satisfies the parameters that I will input in cell C3 to C12 in sheet 2 based on the data in sheet 1.

2)The stocks should then be populated in cell B15 downwards in sheet 2. If there are 20 stocks that satisfies the parameters in cell C3 to C12 in sheet 2, then all 20 stocks should be populated in cell B15 downwards in sheet 2.

3) The respective details of the stocks should also be populated in cell B15 to L15 for all stocks that satisfy the parameters entered in cell C3 to c12 in sheet 2.

I have a master list of people. (call it master) I have 1 other worksheet that should contain everyone aged 15 and up (call it must progress). One of the columns on the master show's their ages. I was hoping excel would automatically populate the sheet based on the age from the master list. So, for example. On the Master list, Joe Smith is 17. I would like Each cell in the Joe Smith Row to automatically copy itself to the must progress worksheet because his age is over 15. The formula would be based off the age on the master.

I am looking for a VBA that could populate from column (A2:A5) to cells (B2:F5) as in the attached spreadsheet, based on the frequency, in a five year time-frame.

I've been trying to get a table to populate based on a couple of criteria. However, I've not come-up with the solution yet.

I have my dropdown selections in cells C2 & C3. The objective is to populate the table below the dropdown with data from the sheet named (very unimaginatively) 'Data'. Currently you see the selection AA-11 & Mar-14 in the Contract ID & Month cells. If I change this, the table below should auto-populate.

I've attempted using Vlookup, Index-Match. But it does not give me the desired result.

Also, the number of Products can change each month (although the file shows 4 for each month & each Account).

I am after a VBA code that I can use to populate a fax template based on criteria's

Basically I have records which are being logged throughout the day via a User form and sometimes there may/maynot be a charge. the user decides at the time. The column is named "to be charged" and is filled with either Yes/ No.

I need to be able to select the date to print or Just the same day date and print all faxes' that have "Yes" for charge This way all the information for each fax is populated and the user can just print.

and if possible mark a Colum non the master sheet as printed with a X or something

I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.

Here is my formula... {=MAX(IF(All_Tickets_07012014!$D$2:$D$155685>1/1/2014,IF(All_Tickets_07012014!$O$2:$O$155685=$A2,All_Tickets_07012014!$G$2:$G$155685)))}

The data... $D$2:$D$155685 - is the list of dates per ticket. $O$2:$O$155685=$A2 - Are a variety of clients, where $A2 is a name of one of the clients. $G$2:$G$155685 - Being a range of time stamps.

The formula runs, however it only shows the MAX of all entries, not within the specific date range...

In theory I know what I should do, but I don't know the syntax. So here it is:

There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)

COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)

basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)

s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate). Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).

In the attached Exel work book I have work sheets named

Material Usage – Usage of materials Estimate - calculate the Total material cost for a job Material Cost – defines the material cost for each material type

In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.

Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .