Solved

Excel template - need help with formula design and IF statement

Posted on 2012-04-12
10
372 Views
Last Modified: 2012-06-21
I need some help with the attached template please.  Code/formulas required to:

1. Auto line count formula from cell A2 in the worksheet "Fund ref database"

2. In cell C66 of the "Fund Template", I need the fund limit to be programmed to automatically show 5%, 7% or 10% depending on if cell C26 has either "Money Market", "Fixed Income" or "Equity" selected respectively.  I cannot remember how to write the IF statement for this task.

Thanks.
Template-design.xls
0
Comment
Question by:abdb469
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37837623
Enter this in A2 and copy down to as far as required for the counting

=IF(D2="","",A1+1)

Use this for the values

=VLOOKUP(C26,{"Money Market",0.05;"Fixed Income",0.07;"Equity",0.1},2,0)
0
 
LVL 8

Assisted Solution

by:ozzeczek
ozzeczek earned 50 total points
ID: 37837682
2:
=IF(C26="Money Market";"5%";IF(C26="Fixed Income";"7%";IF(C26="Equity";"10%";"")))
0
 

Author Comment

by:abdb469
ID: 37837816
ssaqibh,
Both your suggestions work, thanks.  However for 1, can the formula be amended so that if one of the rows are deleted or a new one added, it automatically adjusts itself.

I should have made more clearer perhaps that the line count doesnt have to be dependent on col D the app reference.  

Basically what I am trying to achieve (over many EE questions), is the ability to build a template with macros from a form when completed.  The Fund ref database worksheet will simply be the output of that result i.e. extra lines will be generated .  Of course I understand that th eline count needs a reference point in which to know when to stop counting so that makes it a little more tough.  How about a simple line count that when I drag the rows after inserting a new row, all the numbers remain sequential still?

ozzeczek,
Unfortunately your formula did not work.  It halts on the word Market in the formula.

Thanks.
0
 

Author Comment

by:abdb469
ID: 37837855
I forgot to also add, when the spreadsheet is first opened, cell C26 will be blank until an item is selected from the drop down.  Until then, the formula in C66 will display #N/A.  Until a value above is entered, can we show that as blank rather than #NA?
0
 
LVL 8

Expert Comment

by:ozzeczek
ID: 37838008
Really? i copied it again to your .xnl and it works.
f you choose Money market c66 show 5%....
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 350 total points
ID: 37838020
=MAX(INDIRECT("A1:A"&ROW()-1))+1

=IF(ISERROR(VLOOKUP(C26,{"Money Market",0.05;"Fixed Income",0.07;"Equity",0.1},2,0)),"",VLOOKUP(C26,{"Money Market",0.05;"Fixed Income",0.07;"Equity",0.1},2,0))
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37838040
The reason why ozzeczek's solution does not work is that his system uses semicolons(;) instead of commas(,).

Change all the semicolons to commas and it will work.
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
ID: 37838691
To help to make future proof the lookup for the % rates could be linked to a small table of variables:

A                       B

Money Market  5%
Fixed Income    7%
Equity               10%

Then change the lookup to refer to the range rather than being hardcoded within the formula, assuming table above is A2 to B4:

=VLOOKUP(C26,$A$2:$B$4,2,FALSE)

Future changes can then be made simply by changing the value in the table.

To allow for C26 being blank, you can either use the IF(ISERROR(...) syntax as above or:

=IF(C26="","",VLOOKUP(C26,$A$2:$B$4,2,FALSE))

If using 2007 or later you can also use:

=IFERROR( formula , false result )

rather than having to use

=IF(ISERROR( formula ), false result , formula )

Thanks
Rob H
0
 

Author Closing Comment

by:abdb469
ID: 37839023
Excellent, thanks this works now for both suggestions.  

Please feel free to look at my next question which is an extension of this one:

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

Author Comment

by:abdb469
ID: 37859395
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

18 Experts available now in Live!

Get 1:1 Help Now