IF Statements - I am over my head, PLEASE HELP 5

[ladyck3]

I'm trying to help someone with a formula and I think I'm... no I am .. over my head. I was given the first condition and told that was what was required, and the formula used worked for what it was, but then I was given multiple conditions and well... I'm not sure how/if it can be done in one formula.

The formula I have that works is as follows:
=IF(B9<=85%,IF(B11<=49999.99,E4*0,IF(B9<=85%,IF(B11<=999999.99,E4*0,IF(B9<=85%,IF(B11>=1000000,E4*0.1%,0))))))

It returns FALSE because none of the conditions in the data examples below are met....
The data (so I can keep my head in sync) that are referred to are as follows:

Business Premium is B11 (Example amount 735,932.16)
Persistency is B9 (Example is 122%)

The conditions are as follows:

New Business Premium Persistency < 85% Persistency 85% to 109.9% Persistency 110% or >
Under $500,000 no bonus 50% of gross bonus 50% of gross bonus
$500,000 to $999,999.99 no bonus 75% of gross bonus 100% of gross bonus
$1,000,000 or more 10% of gross bonus 100% of gross bonus 120% of gross bonus

Please help, I am so over my head on this.... simple formulas I have no problems with, when it gets into so many IF statements, I'm lost and I think this might be more than the maximum 7 allotted IF Statements in a formula so NOW WHAT?

Thanks SO MUCH, in advance for any and all assistance.

LadyCK3

 

[SkipVought]

Hi,

I assume that the Gross is somewhere else and what you need this formula to yield is a percentage to factor awith that value?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Hi,

I am suggesting a matrix approch since you already have the criteria set up that way.

I set your matrix starting in A1 with this data...

0    0.85    1.1  Persist  NewBusiness
0    0.5     0.5           0
0    0.75    1             500000
0.1  1       1.2           1000000

where the first row is the Persistency breaks.

I Used Named Ranges for
B9 - BusinessPremium
B11 - Persistency

I named the DATA in the first row, Persist using menu item Insert/Name/Create - Create name in Right

I named the DATA under NewBusiness, NewBusiness using menu item Insert/Name/Create - Create name in Top Row

Then there are 3 formulas
PersistRef is in C9

=MATCH(BusinessPremium,Persist,1)

NewBusRef is in C11

=(MATCH(Persistency,NewBusiness,1))

And finally the result anywhere you like

=INDIRECT("R"&NewBusRef+1&"C"&PersistRef,FALSE)

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[PepeLeMokko]

You have three persistency values 85,109.9 and 110
Put them in cells f19 g19 h19 in descending order
You have three values for businees premiums 500 K 999K999 and 1000 K.
Do the same in cells e20 e21 e22 in descending order
Fill in th percentage of bonus in the cells where rows and columns cross
The real business premium in b19 and the persistency in b18 and apply the following formula somwhere in your sheet :
=INDEX(F20:H22;IF(ISNA(MATCH(B18;F19:H19;-1));1;MATCH(B18;F19:H19;-1));IF(ISNA(MATCH(B19;E20:E22;-1));1;MATCH(B19;E20:E22;-1)))
This should give you the required percentage
( sorry, I worked just about anywhere in my spreadsheet) If you have problems send me a mail at havea.niceday@swing.be, I will send you the sheet
Good luck

 

[ladyck3]

Thanks for the responses....It is going to take my brain a bit to wrap around all of this, its a bit more than I expected.... ::sigh:: Like I said... over my head on this..... Just wanted you to know that I'm here, I've read this, but have to digest it all....

Ladyck3

(and Good Luck is not near what I need, but thanks for the well wishes)

 

[NeilRP]

To keep your sanity, try arranging your data in a table (maybe in a hidden sheet) and look up the values you want.
The top row allows lookup to work in both directions.
For tidyness' sake, I have named the range nrTable.

0 1 2 3
0 0 85 110
0 0 0.5 0.5
500000 0 0.75 1
1000000 0.1 1 1.2

Then use =VLOOKUP(B11,nrTable,HLOOKUP(B9,nrTable,1)+1) to look up the value you want.

 

[bluedragon2]

=IF(AND(B9<85%,B11<=999999.99),E4,)&IF(AND(B9<85%,B11>999999.99),E4*10%,)&IF(AND(B9>=85%,B11<500000),E4*50%,)&IF(AND(B9>=85%,B9<110%,B11<1000000),E4*75%,)&IF(AND(B9>=85%,B9<110%,B11>999999.99),E4*100%,)&IF(AND(B9>=110%,B11>=500000,B11<1000000),E4*100%,)&IF(AND(B9>=110%,B11>999999.99),E4*120%,)

Blue

 

[Wray69]

Here you Go,

=IF((B11<1000000)*(B9<85%),E4*0,IF((B11<500000)*(B9>=85%),E4*0.5,IF((B11>500000)*(B11<1000000)*(B9>=85%)*(B9<110%),E4*0.75,IF((B11>500000)*(B11<1000000)*(B9>110%),E4,IF((B11>=1000000)*(B9<85%),E4*0.1,IF((B11>=1000000)*(B9>=85%)*(B9<110%),E4,IF((B11>=1000000)*(B9>110%),E4*1.2)))))))

Cheers,

Wray

 

[Zathras]

ladyck3 -- At the risk of further confusion, here is a variation on the theme introduced by Skip Vought. I believe it will be easier to adapt to changes in the future (when additional break points are added or the numbers change). It has the added advantage that the data table can be placed anywhere you want in your workbook.

Set up the matrix this way in A14

         A           B       C       D
1

[blue]

   NBP / Persist     0%     85%    110%

[/color]

2

[blue]

              0      0%     50%     50%

[/color]

3

[blue]

        500,000      0%     75%    100%

[/color]

4

[blue]

      1,000,000     10%    100%    120%

[/color]

If you type the percentages as shown (with the % sign) the cells will be automatically formatted as percentage and stored as decimal for proper calculation.

Assign range names as follows:

  B1:D1  =

[blue]

PersistLadder

[/color]

  A2:A4  =

[blue]

BusinessPremiumLadder

[/color]

  B2:D4  =

[blue]

BonusData

[/color]

This formula will give you the percentage to use and can be placed anywhere:
[blue]

  =INDEX(BonusData,MATCH(B10,BusinessPremiumLadder,1),MATCH(B8,PersistLadder,1))

[/color]

 

[ladyck3]

TO everyone... THANK YOU...
Special Mention (and STARS) for both Bluedragon2 and Wray69 and the reason they get the stars is because it his similar to what I was already working with, without introducing new indirects and hidden stuff with scripts 'n stuff....

Simple yet complicated without making it even more confusing. I can read them, &quot;GET THEM&quot; and they work just like I need them to.... I just don't have a handle on the proper syntax on writing these things. I know what I want them to do, just do not have command of the language to get them to do what I want.

Sorta like speaking in english to someone who speaks French, Spanish, German, Italian...etc and telling them to take off your hat and coat, put them on the rack over there, go to the table, pick up a name tag and then please go sit over there.

No matter how LOUD or SLOW you speak, they still won't understand.... as in normal for when we try to speak to someone who does not speak our langauge...

YELLING at EXCEL was just not working! hehehe

THANKS FOLKS!!! I am always over and above the limits appreciative to all of you....and for all of you, this is a GRAND place for assistance and lending a hand.. I've done both

Ladyck3

 

[SkipVought]

Lady,

I understand the pressure of getting a correct asnwer ASAP.

But let me urge you, without shouting, to consider Zathras' solution (which is superior to mine)

because...

this approch conceptually can easily be extended to greater or fewer breaks with different break values WITHOUT CHANGING ANY FORMULA! (caps for emphasis only)

And using Named Ranges greatly enhances the understanding of whats happining. It goes a long way in self-documenting the process.

So in a moment of quiescence, reflect on the simplicity of this approch and the possibilities it might afford in some other time and some other place. ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Thanks, Skip.

ladyck3 - You will find that both formulas are flawed. Debugging that kind of nested IF is not easy.

For example, put 100 in cell E4, then try the following values in B9 and B11:

1. B9: 85%
B11: 500,000
Should be zero, but wray69 gives FALSE

2. B9: 110%
B11: 1,000,000
Should be 120, but wray69 gives FALSE

3. B9: Anything < 85%
B11: Anything < 1,000,000
Should be zero, but bluedragon2 gives 100

4. B9: 85%
B11: Anything < 500,000
Should be 50, but bluedragon2 gives 5075

Working with a matrix of data in conjunction with the INDEX and MATCH functions provides a consistent solution across all data points. Boundary conditions are extremely difficult to cope with in nested IF statements.

With that said, here is a slightly modified form of my function (I had inadvertently inserted a row in my worksheet and it threw the cell references off by 1.) Also it includes the multiply by E4 in order to provide the data in the exact form you want:
[blue]

=INDEX(BonusData,MATCH(B11,BusinessPremiumLadder,1),MATCH(B9,PersistLadder,1))*E4

[/color]

...similar to what I was already working with...

I would submit that the fact that you were having a difficult time making it work would be a reason AGAINST using a method that is similar.

 

[bluedragon2]

Z is right, the first IF should have E4*0 and the forth IF should include a B11>500000 in the AND statement

They are also correct in using the INDEX lookup way. It is cleaner and can be adjusted so much easier. But, if you like the IF way, I was glad to help, cuz I just love making formulas.

Blue

 

[ladyck3]

Well goodness, thanks for staying on my tail... I'm going to really take a closer look at this, I promise... so look forward to probably more questions....

I appreciate that you are keeping on me about this... i honestly do.... give me til sometime tomorrow.... bug me if you don't hear from me, I get bizzy or is that dizzy? oh.. BOTH! <=== for major emphasis LOL

I appreciate you! I do!

Ladyck3

 

[ladyck3]

Morning all.... well here I am, confused as ever...

I set up the matrix as you suggested, Zanthros.... and I did it on a second sheet because the data I'm using has info in that section... as long as we have named ranges in the workbook it really doesnt matter where they are, correct?

Anyway, I used the first =INDEX formula that you posted and got an #N/A so I tried the second modified one... and again, same result... so... my question is...

What in the HECK am I DOING????

Thanks so much for your patience...

(I can do a named range, I know that much, honest but never did an Insert/Name/Create then give it a location? What is that for?)

 

[Zathras]

ladyck3, Insert/Name/Create is a special feature which does not apply in this situation. Insert/Name/Define is a way to create a name for a range. So, if the matrix is on Sheet2, the definitions would be as follows:

  PersistLadder          =Sheet2!$B$1:$D$1
  BusinessPremiumLadder  =Sheet2!$A$2:$A$4
  BonusData              =Sheet2!$B$2:$D$4

And yet, I will show you a more excellent way:
Simply select the cells which you want to name, and then in the combo box where you usually see cell addresses, type the name you want to assign and voila!

For example, select the cells A2:A4. Notice that before you release the mouse button the combo box shows 1R x 3C indicating that you are selecting 1 row by 3 columns. Notice again after you release the mouse buttton the combo box shows A2, the upper-left corner of the selected area. Simply replace the A2 with BusinessPremiumLadder and press the Enter key. If you now select Insert/Name/Define from the menu you should find that named range correctly defined.

After you finish defining the three ranges and paste the formula from above into a cell on Sheet1 (or Sheet2 - it doesn't matter) it should correctly find the matrix and calculate the result you need.

Tip: Look in the help file for the various Insert/Name options. It's a bit of a mess in the organization, but if you use the Help/Index for &quot;naming,ranges&quot; and click &quot;Display&quot; you should get a pop-up that allows selection of &quot;Name cells in a workbook&quot; which should give you a list of topics you can explore. Try &quot;Name cells by using existing row and column labels&quot; to see about Insert/Name/Create. (This is with Excel 97) If that doesn't work, try Help/Find for &quot;insert name create&quot;, select &quot;... Create&quot; from box #2 and &quot;Name cells by using existing row and column labels&quot; from box #3.

 

[bluedragon2]

I think your problam may be in naming the ranges. An easy way to name a range is to select the range you want to name (highlite the entire range). Then, in the dropdown box to the left of the data entry area(should be the cell of the first location of your range), type in the name you want it to be called.

Blue

 

[ladyck3]

OH MY GOD.. I got it to work!!!! GO ME...Yipee!!!

Now that I have it working, I'm going to go back and understand what I did or 'read' the formula and comprehend it.

Zan, Skip, Blue.... stars to you all... thank you SO MUCH for your patience....

I guess we can close this thread... unless I have some sort of question, but a lightbulb went on this last time I was doing the formula and naming the ranges so I might just be ok with it

Also, the Insert/Name/Create was mentioned, I think by Skip, earlier in all of this and I made a side note in the previous post, just did not mention it was a side note... you'd have to know me, that's my M.O. always shifting gears and expecting everyone to follow along sorry to catch you out of the loops in my head

Gosh this is GREAT! Thanks again!!! I have some stars to dole out.... YIPEE!!!

You guys ROCk!