Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Nested IF statement to return a value based on multiple criteria

Hello Excel Gurus,

I am trying to return a value based on multiple criteria using a nested IF statement. Basically, I have a table that contains costs for a direct mail campaign, and I am trying to return the cost of the program based on the values that can be selected in two separate cells which both have drop-down lists. There are 20 different possible values that can be returned based on the possible combination of selections in those two separate cells.

If you look at the attached spreadsheet, the cell that I am trying to return the costs to is cell B4 (Total Program Costs). The possible values that can be returned are all in column G.

The statement should return values based on two criteria:

1) It should compare the value of B2 with the values in Row F
2) It should also compare the value of B3 and compare with the values in Row A.

It should then return the value based on the way the table is set up.

So, if a user selects "Long Mailer" in cell B3, and 100,000 in B2, then cell B4 should return a value of $43,300.

If a user selects "Jumbo Postcard" in cell B3, and 25,000 in B2, then cell B4 should return a value of $11,500.

I attempted to solve the problem using a really long nested IF statement using the AND command as well. I got an error message in Excel telling me that I had exceeded the max amount of levels in a nested statement. I am assuming there is a more elegant way to do this, but I cannot figure it out.

Help!

Thanks.


Post your answer or comment

comments powered by Disqus
I need to create a formula to return a value based on 3 criteria.

if A1 < 75 return "9" in B1
if A1 is between 75 & 95 return "3" in B1
if A1 is > 95 return "1" in B1

Thanks in advance.

Mike

Want to return a value based on a whether a number is within a range of
numbers using the IF function. Can someone please give me the correct syntax.

Hi I hope someone can help me with my dilemma

I'm trying to return a value based on a range of data. For example

Amount Amount2 Value
1000 2000 1
3000 4000 2
5000 6000 3

Salary Value
3200 2 - What is the formula to return this?
1800 1
1200 1

Thanks in advance!

I want the return a value based on the status of a check box or radio button.
If checked = true then 1 else 0

I would like to have a formula that would return a value based on the
following criteria

A1>1000 or B1>4% return a value of 25
A1>1500 or B1>5% return a value of 50
A1>2000 or B1>6% return a value of 75

Here's the tricky part. I always want it to return the higher value. In
other words if the value in A1 is 1100 but the value in B1 is 5.5% then I
want it to return a value of 50.

Okay here goes;

I have a table that contains training data received from the business. I need to cross reference an employee list with this table and have it return values based on multiple criteria. I've tried combining VLOOKUP and IF, I've also tried using the INDEX, MATCH functions and I've made some unholy combinations of many others.

The one that works the best seems to be a combination of IF & COUNTIFS.

=IF((COUNTIFS('DoC Results'!$C$2:$C$581,A4,'DoC Results'!$U$2:$U$581,"Completed"))>0,"Completed","Incomplete")

True values are returned in all my tests.

First problem;

In the next column (beside the one in my forumla), I'd like to display a specific column value (it's a date) from any records that meet the criteria i.e =1

Second Problem;

Some employees have done the same training twice i.e. >1. I'd like the above formula to return the most recent date of all the records that are counted. I've read about the MAX function but can't use it until I have a formula that returns a value (Problem 1)

I'd love to post a sample of the workbook, but it'll take more time than I'm willing to give to mock-up values. Let me know if you want me to post the actual spreadsheet.

Please help!!!!!!

I need help devising formulae/VB to do the following:

I have a database list that I copy into Excel as a worksheet. In excel, I
have many different worksheets that do calculations based on this list.

Example database list:

Dept Pay Grade Count Avg. Wage
IT 5 2 50
IT 6 3 75
HR 4 1 40
HR 5 5 50
Sales 8 1 100

How do I:
1) From a cell in my summary report, I want it to automatically lookup how
many people in IT are Pay Grade 6 (i.e. return the count for the row where
the Dept and Pay Grade criteria are met).

2) From another cell in my summary report, I want to automatically lookup
what the average wage is for people in HR who are Pay Grade 4. (i.e. return
the avg wage for the row where the Dept and Pay Grade criteria are met).

Keep in mind my that I frequently need to refresh my database list so I
don't want to keep changing 50 cell references in each of 12 different
worksheets every time I reload the list. I need it to automatically look
this info up from the same range of cells where I always paste updated lists.

What formula can I use to return a value based on three conditions

I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:

Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".

Is this possible? Thanks in advance for any help as I am absolutely stumped.

Ok, I've searched the forums, googled it, tried several functions on my own and I just can't figure this one out.

Here's what I'd like to do and thanks in advance for any help. It will really put my mind at ease if I can get this spreadsheet working.

Basically I have various dates in a spreadsheet. If that date for example falls in between March 28th and April 3rd inclusive, I'd like to return a value of "3/28/10" which represents the particular "week of" that the particular dates falls into.

So whether the date is 3/28, 3/29, 3/30, 3/31, 4/1, 4/2, or 4/3, I'd like to return the value of 3/28. If the date is between April 4th and April 10th then I'd like to just return the value 4/10. I've tried nested ifs, various date functions, and just can't anything that works right =)

Thanks!!!!

I am trying to assign a specific value to a cell based on multiple
choices.

For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
This value is based on what is contained in cell B1.

If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"

I've tried a number of different formulas, but can't make it work with
=IF alone, since it only returns one of two values based on a single
condition.

How do I make the formula return one of four values based on four
different conditions?

I have to have this on another desk by tomorrow morning! Any help
anybody can give would be much appreciated, and then some! Thanks in
advance,

Mike Simard

Is there a formula that will return a value based on a cells background color.

I have a spreadsheet that someone has taken the time to highlight changes they want. I want to be able to identify these and be able to filter on them. I assume the easiest way is to assign a value in an adjoining column.

I know I can do this the other way around by using conditional formatting, but don't know if what I want can be done.

Thanks.

I am trying to assign a specific value to a cell based on multiple
choices.

For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
This value is based on what is contained in cell B1.

If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"

I've tried a number of different formulas, but can't make it work with
=IF alone, since it only returns one of two values based on a single
condition.

How do I make the formula return one of four values based on four
different conditions?

I have to have this on another desk by tomorrow morning! Any help
anybody can give would be much appreciated, and then some! Thanks in
advance,

Mike Simard
mike@streffco.com

I am at my wits' end here. I'm trying to use several criteria to put information in one of two columns. This is a description of what I am trying to do. Please let me know if there is a way for me to attach a file to make it more clear.

1. Column E gives the number of units.
2. Column J gives the size of the family members (the number of people).
3. L gives annual income.
4. M is the year - when this changes, so do values in columns P:W, as you've noticed.
5. N:O are fields that I want to fill in. In each row, either N or O will be identical to the number in E. In no cases should both N and O have a number in the same row.
6. P:W are the income thresholds.

The idea is to fill in N:O in the following way:

J2 indicates that there are three family members. This being the case, I want to compare L2 (annual income) to R2 (Very Low income threshold for a family of 3). If L2 is less than R2, I want N2 to display a value identical to E2. If L2 is more than R2, I want N2 to say "0."[img][/img][img][/img]

I need help trying to return a value based on 2 conditions.

Based on the table below i need a formula to return the value where the Category (A,B,C,D) meet the percentage.

Eg if % is 5% (between 0% and 10%) and is a Catergory C then i want to return the value 1.0.

Think that this is quite an easy function however i just cannot get my head round it.

Appreciate any help on this.

--------------------- A - B - C - D
between 0% 10% 0.5 0.8 1.0 1.3
between 11% 20% 1.0 1.3 1.5 1.8
between 21% 30% 1.5 1.8 2.0 2.3
between 31% 100% 2.0 2.3 2.5 2.8

Hi

I want to return a year based on a date that is in cell A2. The criteria is below. Once the formula which would be in b2 is correct I would copy it down to the other rows so that a year is returned based on the date in the cells in colum A.

>30/06/2008 but <01/07/2009 = 2009
>30/06/2009 but <01/07/2010 = 2010
>30/06/2010 but <01/07/2011 = 2011
>30/06/2011 but <01/07/2012 = 2012

Any Help is much appreciated

Thanks

I would like to sum a column of values listed in a table based on multiple criteria. I have created cells for the user to input the varying criteria. I'm trying to use a sumproduct formula to evaluate the criteria and calculate the filtered summation. The problem that I have is that not all the criteria will be used. If one of the criteria inputs is left blank, the return value is always "0". How can I setup the formula to ignore the blank cells? Please let me know if there is a more efficient method to complete this calculation. Thanks for your help.

Here's the formula: =SUMPRODUCT((B4:B19=I3)*(C4:C19=I4)*(D4:D19=I5)*(E4:E19))

I need to get a count based on multiple criteria. I need to count if a
certain text value occurs in a column, but only if it meets a seperate date
criteria.

I have a data entry page with a vendor column and and month column. I want
to count how many times a vendor is listed in a certain month. I have tried
Count and Count IF formulas with out luck.

Hello,

I am trying to write a function that calculates the number of weeks between two dates based on several conditions. The function I wrote does not work for all the condtions and returns impossible values:

=if(AA80,if(f80,if(E8>AJ8,round(days360(E8,AH8)/7,0),round(days360(AJ8,AH8)/7,0)),if(E8>AJ8,round(days360(E8,AH8)/7,0),round(days360(AJ8,AH8)/7,0))),if(f80,if(E8>AJ8,round(days360(AJ8,AA8)/7,0),round(days360(E8,AA8)/7,0)),if(E8>AJ8,round(days360(E8,F8)/7,0),round(days360(AJ8,F8)/7,0))))

There are only seven nested If statements, but should I try using the CONCATENATE function for this?

Here is an example for one row:

For AA8 = 12/04/04, F8 = 0, E8 = 04/01/65, AA = 12/04/04, AJ8 = 10/04/04, and AH8 = 10/02/05, the function should return 9 weeks. Instead it return 51. AH and AJ are constant dates representing the ending and beginning of the fiscal year respectively.

I am not familiar with writing VBA code. I would be very appreciative if anyone could give me any advise on how I might get this to work.

Thank you!

I have created the following formula:
=IF(I6="Adopted as Is","None"),IF(I6="Adopted w Modification","Modification"),IF(I6="Add","New Build","")

What I want is that based on what is inputed in cell I6 I want a certain value returned in cell Q6. Cell I6 has a pick list with 5 choices that can be selected. Each one of the selections will cause a different response in cell Q6.

when i entered the formula above it returned a #value! error. Can someone help this newbie!!!!!

I'm trying to create a formula to return a rating/table value based on a % calculated from another cell. If' the cell value (E5) was 85%, a rating of 7 would be returned in another cell (F5) based on the table below.

Table
Max Min Rating
100% - 100% 10
99% - 95% 9
94% - 90% 8
89% - 84% 7
83% - 76% 6
75% - 68% 5
67% - 55% 4
54% - 42% 3
41% - 30% 2
29% - 15% 1
14% - 0% 0

Thanks,

Mike (Hackdogg)

trying to have my formula to either give me an account number or D9 is blank
return a value of "blank"

=IF(D9="040410",411000,IF(D9="040428",420026,IF(D9 ="040403",420011,IF(D9="040405",420028,IF(D9="0404 07",420013)))))
--
Cindy

I want to return a value (a number) based on a number in anotehr cell that is
within a range of numbers using the IF function. For example: A2=$75 if B2=5,
A2=$100 if B2=10, A2=$150 if B2=15.

Can someone please give me the correct syntax, I am really lost!

I have a worksheet where one column shows a code followed by one of four options (Deliverable 3, IFC Engineering Workpack complete, IFC Construction Workpack, Land Access Granted - eg (McFarland D.10RP185773) Final IFC Engineering Workpack Complete) which are generated using a vlookup function. In the adjacent cell, I need to find a function which will return a letter based on which of the four optios is displayed, but as the code at the start of the text is necessary for ID, the funtion needs to look at whether the cell contains certain words. I tried using the IF Function, but can only return one result.

I need the function to do the following:
If cell contains Deliverables, return "a"
If cell contains Engineering, return "b"
If cell contains Construction, return "c"
If cell contain Land, return "d"

I tried =IF(ISNUMBER(SEARCH("Land",F2:F3)),"d","") but got stuck there. I cannot get my head around how to return a different value based on which of the four options appears in the cell.

I really hope that makes sense! If anyone has a suggestion I would be really appreciative.

In Excel 2007, I need to be able to return a value (definition of something) based on what is selected in 2 other cells.

(See Attached)
If in cell C2 I select "competency 1" and in Cell C3 I select "Expert", I need to be able to return the definition for someone that is an Expert in Competency 1 in cell D2.

Then, if in cell C5 I select "competency 2" and in Cell C6 I select "Leading", I need to be able to return the definition for someone that is Leading in Competency 2 in cell D5. And I'll be doing this about 10 times on each sheet.

All the tables with info are in another worksheet. I can do a vlookup if I'm just using one thing to match, but how do I match 2 things to return the definition?

Any help would be appreciated.


No luck finding an answer? You could always try Google.