Solved

Help with a long if then else statement in Excel using VBA

Posted on 2012-03-26
10
350 Views
Last Modified: 2012-06-21
I am hoping this will be decently easy for you experts!! I have an excel formula that already exists in my spreadsheet. The problem that I am having is that I need to add quite a bit more logic to it. I have a variable named months this is tied to a spreadsheet in the workbook.. "bookmarks" is the name. Here is the formula now..

=IF(ISNA(VLOOKUP(Years,Bookmarks!C39:D43,2)),1,VLOOKUP(Years,Bookmarks!C39:D43,2))

I need to add to this formula

If month (- this is the variable name) =

0 to 11     then 0
12 to 18   then 1
19 to 30   then 1.5
31 to 42   then 2
43 to 54   then 2.5
55 to 60   then 4

can someone help me with this?? Pretty Please??
0
Comment
Question by:Jennifer Barman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 9

Expert Comment

by:OCDan
ID: 37768155
Do you have some sample data to look at? And what you would expect the result to be given certain data?

Sorry for the questions I am just pretty unclear on what you are after here.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 37768247
I see you're returning "1" if the VLOOKUP fails.  

Are now wanting to return one of the possible corresponding values in your list that depends on the value for the variable "months"?

If so, the formula would be simpler (and easier to edit) if you had another lookup list to use for "months" and its lookup value.

-Glenn
0
 

Author Comment

by:Jennifer Barman
ID: 37768256
well this populates to a cell.. and is figured into a  calculation after the fact.

I am not extreamly familiar with vlookup or how the first statement was made. All I need to do is add the information below to the formula bar.. something like a nested if then??

Not really sure what you are wanting for example data. Right now it is creating a multiplier on our commissions sheet.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jennifer Barman
ID: 37768271
I actually have created a list in bookmarks.. I guess I am not sure how to write this out?? I can give you the cell names? first the list also comes from bookmarks.. the cell range is P16:P21
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 37768319
Okay, if P16:P21 is the list for the month range, you need to have the appropriate multipliers adjacent to them.  Like so:

P16 = 0
P17 = 12
P18 = 19
P19 = 31
P20 = 43
P21 = 55

Then, the multipliers
Q16 = 0
Q17 = 1
Q18 = 1.5
Q19 = 2
Q20 = 2.5
Q21 = 4

Then, replace the "1" in your original formula (the "True" condition of your IF statement) like so:

=IF(ISNA(VLOOKUP(Years,Bookmarks!C39:D43,2)),VLOOKUP(months,$P$16:$Q$21,2,TRUE),VLOOKUP(Years,Bookmarks!C39:D43,2))

Of course, I'm still assuming you're replacing the error value of "1" with the multiplier based on the months value.  If that's incorrect, you'll need to further clarify what you want to do.

-Glenn
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37768365
You can use this formula without using a range

=VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2)

if you need a not found condition you can use

=if(isna(VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2)),1,VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2))

or in 2007 or later

=iferror(VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2),1)
0
 

Author Comment

by:Jennifer Barman
ID: 37768620
I tried the above query (Glenn).. following your directions.. but it now jsut say #N/A.. :(

any ideas?

ssaquhb... can you figure out how to add my existing formula with my new data.. I have to have the old data in the collection.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 37768673
CJ,

In any blank cell, insert the following formula:

=VLOOKUP(months,Bookmarks!$P$16:$Q$21,2,TRUE)

If the range/variable "months" is not blank and is a number, AND if you populated the range of P16:Q21 as I noted above, you will get a result.  The "Bookmarks!" part here may be what is missing.

-Glenn
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37769364
=IF(ISNA(VLOOKUP(Years,Bookmarks!C39:D43,2)),1,VLOOKUP(Years,Bookmarks!C39:D43,2))+if(isna(VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2)),1,VLOOKUP(month,{0,0;12,1;19,1.5;31,2;43,2.5;55,4},2))
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 37772665
Just looking at some of the solutions provided and I notice that only Glenn's comment 37768673 has the TRUE/FALSE indicator for the lookup formula.

In theory without the indicator the lookup will never fail because it will return the closest match therefore the IF(ISNA(VLOOKUP...) will always go to the second option as the ISNA condition will not occur.

Thanks
Rob H
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

635 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