Solved

Question      Ask A New Question  Excel template - need help with formula design and IF statement III

Posted on 2012-04-12
5
238 Views
Last Modified: 2012-04-18
I need some  help with a formula to do the following please on the worksheet titled "Fund Template".

If cell C60 displays the word "Big", then cell C67 (the Fund Credit Limit ceiling) should display a limit of either 75, if cell C56 ALSO shows a rating of either AAA. AA+, AA, AA-, A+ or A or show a limit of 60 if it shows A-, BBB+ or BBB or show a limit of 50 if it is BBB-, BB+ or BB.

If cell C60 displays the word "Medium", then cell C67 (the Fund Credit Limit ceiling) should display a limit of either 75, if cell C56 ALSO shows a rating of either AAA. AA+, AA, AA-, A+ or A or show a limit of 40 if C56 shows A-, BBB+ or BBB or show a limit of 25 if C56 is BBB-, BB+ or BB.

If cell C60 displays the word "Small", then cell C67 (the Fund Credit Limit ceiling) should display a limit of either 50, if cell C56 ALSO shows a rating of either AAA. AA+, AA, AA-, A+ or A or show a limit of 20 if it shows A-, BBB+ or BBB or show a limit of 5 if it is BBB-, BB+ or BB.

Basically what the above is saying, is that depending on the both the rating of the parent (could fall in one of the three categories) as well as the size of the AM, the limit could be X, Y or Z.

Please ensure that the cell C60 displays blank if there is no data entered above i.e. dont want to see #N/A if the spreadsheet has just been opened.

Thanks.
Template-design-2.xls
0
Comment
Question by:abdb469
  • 3
  • 2
5 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 37840498
I put a lookup table (called RatingTable) in your "Field names for lists" worksheet. This then allowed me to put a data validation dropdown  in cell C56 and use the following formula in cell C67:
=IF(OR(C56="",C60=""),"",VLOOKUP(C60,RatingTable,MATCH(C56,INDEX(RatingTable,1,),0),FALSE))

The seeming mistake with the data missing after the comma in INDEX is intentional. You need it that way for the formula to work.
Template-design-2Q27673394.xls
0
 

Author Comment

by:abdb469
ID: 37841492
Top man, that's pretty neat.  How long did it take you to do that?

Also just for my education, could you please walk me through that formula in layman terms?

Thanks!
0
 
LVL 80

Expert Comment

by:byundt
ID: 37842119
RatingTable        is a named range containing your lookup table on "Field names for lists" worksheet
INDEX(RatingTable,1,)        INDEX function can be used to return a single cell from named range RatingTable, an entire row (as in this case) or entire column. By not specifying the third parameter (for column), I get the entire row.

MATCH(C56,INDEX(RatingTable,1,),0)      MATCH searches that row for the value in C56. As such it returns an index number for the desired column in RatingTable.
VLOOKUP(C60,RatingTable,MATCH(C56,INDEX(RatingTable,1,),0),FALSE)        VLOOKUP returns a value from a row of RatingTable that matches the value in C60. The column number is specified by the MATCH.

OR(C56="",C60="")        returns TRUE if either C56 or C60 is blank or equal to an empty string
=IF(OR(C56="",C60=""),"",VLOOKUP(C60,RatingTable,MATCH(C56,INDEX(RatingTable,1,),0),FALSE))           IF returns an empty string (looks like a blank) if either C56 or C60 haven't been specified. Otherwise, you get the desired value from RatingTable.

The formula took less than a minute to write. Including reading your question enough to catch your objective, set the problem up and post the solution--probably 10 minutes. I've done these before :-)

Brad
0
 

Author Closing Comment

by:abdb469
ID: 37845903
Thanks again.
0
 

Author Comment

by:abdb469
ID: 37859398
Hi byundt,

Is there a chance you could help me with the following question?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27679826.html

Thanks.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now