Free Microsoft Excel 2013 Quick Reference

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.

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.

- Return a value based on multiple criteria
- Want to return a value based on a whether a number is within a ra.
- Formula to return a value based on a range of sale
- I want to return a value based on the status of a check box. If .
- Returning a value based on mulitple criteria
- Returning specific values based on multiple criteria in a tablewith duplicate entries
- Return a Value Based on 3 Critera
- Is it possible to return a value based on cell format?
- Want to return a value based on date range
- How do I return a value based on multiple possible conditions?
- Return a value based on cell background color
- How do I return a value based on multiple possible conditions?
- Need to return a value based on several criteria
- Looking up a value based on 2 conditions
- Formula to return a year based on a date range
- Summation of values based on multiple criteria & ignoring blanks
- Counting and Suming based on multiple criteria
- Nested IF statements to calculate # of weeks based on various dates
- Using If statements to return a value
- Formula to return a value based on a table range
- Using a IF statement to return a value
- Return a value based on a range
- Function to return different values based on adjacent cell
- Return a value based on 2 items from list

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

numbers using the IF function. Can someone please give me the correct syntax.

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!

If checked = true then 1 else 0

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.

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

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.

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!!!!

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

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.

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

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]

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

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

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

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.

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!

=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!!!!!

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)

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

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

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