Multi-Condition IF AND Formula/Function

Oct 25, 2006

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?

View 6 Replies


ADVERTISEMENT

Multi Condition Lookup Formula/Function

Dec 11, 2007

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 Related

Multi Condition Formula

Feb 6, 2007

I am working on a formula that has just gotten beyond me. This is what I want it to do:

If:

=IF(K9<I9,

Then:

IF((I9-H9+T8+U8)>=180,(180-T8-U8-(K9-J9)),(I9-H9)),IF((K9-J9)+(I9-H9)>=180,IF((K9-J9)>=180,0,180-(K9-J9+T8+U8)),

Else:

IF(I9-H9)>=(180-T8-U8-(K9-J9),(180-T8-U8-(I9-H9),(I9-H9))


This is what I currently have, but it's not working:

=IF(K9<I9,IF((I9-H9+T8+U8)>=180,(180-T8-U8-(K9-J9)),(I9-H9)),IF((K9-J9)+(I9-H9)>=180,IF((K9-J9)>=180,0,180-(K9-J9+T8+U8))),IF(I9-H9)>=(180-T8-U8-(K9-J9), (180-T8-U8-(I9-H9),(I9-H9))))

View 8 Replies View Related

Formula To Return Successive Multi Condition Look-up Results

Aug 24, 2009

Currently for the first line on the sheet I'm working with, I have the following:

=INDEX(SALESMEN!$D$2:$D$500,MATCH(TRUE,SALESMEN!$H$2:$H$500<>"X",0))

which is completed with CTRL+SHIFT+ENTER, and it works properly. However, I want to do the same for all lines following, where it will go to the next item matching that value. For instance: on my SALESMEN sheet, I have John Smith, Fred Johnson, and Mary Williams. Fred Johnson is considered inactive, which is represented in the SALESMEN sheet as an "X" in column H. Therefore, I want line 1 to show John Smith, and line 2 to show Mary Williams.

View 9 Replies View Related

Multi Condition Format? Lookup And Date Condition Valid For

Aug 13, 2009

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 Related

Multi-function Formula (=IF) (for Specific Shipping Weights)

Feb 3, 2010

I would like to write a formula for shipping cost in Excel 2003, for specific shipping weights. Each product is a different weight. The result is in a new column.

What formula would I use for this column? The figures below are hypothetical.

A1 is 4 lbs. Shipping is $6.00
A2 is 8 lbs. Shipping is $12.90
A3 is 10 lbs. Shipping is $16.00
A4 is 15 lbs. Shipping is $24.00

Formula for the column is ?

If A1 is greater than 1 lb. and less than 5 lbs. then shipping cost is $6.00 or
A1 is greater than 7 lbs. and less than 10 lbs. then shipping cost is 12.90 or
A1 is greater than 9 lbs. and less than 15 lbs.then shipping cost is $16.00
A1 is greater than 14 lbs. and less than 18 lbs. then shipping cost is $24.50

View 4 Replies View Related

Multiple Condition IF Function And Formula

Mar 28, 2008

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.")))))

View 9 Replies View Related

Multi Condition Sum

Jan 22, 2007

I am trying to sum the error points of staff in their work done based on a specific time frame. For example,from 4.00pm to 4.30 pm as per the attached. I tried sum product but did not work. Is there an addition formula that I need to use when incorporating time ?

View 6 Replies View Related

Multi Condition Sumif

Apr 14, 2008

Is there a way to have a sumif formula dependent on two conditions?

View 9 Replies View Related

Multi Condition Counting

Sep 24, 2009

I have three columns of data

Col-A-----Col-B------Col-C
1---------(blank)-----a
3---------A----------b
1---------A----------b
2---------B----------c
2---------(blank)-----c
4---------D----------a
3---------E----------a
1---------A----------b
3---------B----------c

I am trying to calculate two things:
first, a simple count of entries (non-blank) in Col-B corresponding to 'a' in col-C. Ex. for Col-C='c', the no of entries in Col-B is 2. Here I was trying SUMPRODUCT(--(Col-B range""),--(Col-C range="a")) with CTRL+SHIFT+ENTER. It doesn't work.

second, a more complex count without repetitions. Ex. for Col-C='b' and Col-B='A', the no of entries in Col-A is 1, because there are two 1's. If there would have been three 2's and four 3's the count would be just 2.

View 9 Replies View Related

Create A Multi Condition IF Statement

Mar 26, 2009

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)

View 13 Replies View Related

Multi-Condition Volume Weighted Average Calculation

Sep 13, 2006

I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.

I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):

= SUMPRODUCT(price, quantity)/SUM(quantity)...

View 9 Replies View Related

Multi Condition Date Cell Test For Month & Year

May 18, 2009

Please refer to attached Sinking Fund worksheet. have managed to solve most of it. Just need to figure out how to match the periods out. My requirements are on the worksheet.
1. To get the schedule on sheet 1 to stop calculating further once it has reached the actual number of payment periods as shown in H12.
2. To get the interest calculation in sheet2 to stop once the actual number of payment periods as shown in sheet1 H12 is reached.

View 3 Replies View Related

Multi Argument IF Function

May 23, 2008

formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.

here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5

=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)

this is only 1 case HOW can i calculate it basd on 9 casses??

View 10 Replies View Related

Multi Level Match Function

Nov 19, 2009

Been trying to figure out whats wrong with my formula. The 4th column doesn't seem to produce the intended result. I have attached here for reference.

View 4 Replies View Related

Multi Conditional IF Function: Show Where The Tender Ranks Against A Set Of Criteria

Nov 12, 2009

I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.

View 3 Replies View Related

Return Row Index Of Selected Items In Multi Column Multi Select Listbox

Jun 30, 2014

I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:

[Code] .....

But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.

View 5 Replies View Related

Function Sumif() That Used For Do Sum() Under Condition

Jan 9, 2007

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().

View 9 Replies View Related

Rank Function With Condition

Apr 25, 2006

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 Related

Multi-cel Array Formula Not Working

Jun 21, 2009

See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour.

Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.

View 4 Replies View Related

Annotate A Multi-line Formula?

Jan 29, 2010

I like to write multi-line formulas like this:

View 3 Replies View Related

Multi Criteria Sum Formula Very Slow

Aug 22, 2009

I have previously been performing a multi level SUMIF of named ranges with a large data range.

This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria.

Basically, i'd like to select a range of rows which will parse into the SUMIF formula.

I have solved this by referencing an external cell to create the range.

View 6 Replies View Related

Multi-Conditional Table Formula

Feb 15, 2010

I'm trying to add "yes" as per the attached spreadsheet, using forumla. Account codes are duplicated (sometimes more than once, although no shown on the example) each account code needs to have the same yes column.

View 3 Replies View Related

Multi-result Array Formula With Max

Jul 20, 2007

I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into.

Example for A1=2,B1=4

=2*A1:B1

array enetered gives 4,8

However

=Max(2*A1:B1,5)

array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.

View 6 Replies View Related

1 Column Into Multi-columns And Multi-rows

Jul 16, 2013

Let's say I have one column of;

1
2
3
4
5
6
7
8
9

What is the most efficient way to change this into '3-columns & multi-rows' like this?:

1 2 3
4 5 6
7 8 9

The actual list is a lot longer and numbers are not in order.

View 7 Replies View Related

Concatenate Multi-row Cells Into One Multi-row Cell

Aug 21, 2013

I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:

Desired Result

First
First
Name
Name
First Name
First Name

View 2 Replies View Related

Adding Another Condition To Sumif Function?

Jan 8, 2014

Below 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.

View 2 Replies View Related

IF Function With A Condition From Reference Cell?

Feb 19, 2014

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?

View 4 Replies View Related

Sumif Function Condition Cell Value

Nov 24, 2009

I 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 Related

Multi Criteria With MATCH Or LOOKUP Formula?

Mar 20, 2014

What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..

and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.

and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved