Nested If Statement

**soniarog** · 03-08-2013, 02:30 PM

Hi, I need some help making this statement work.

+CEILING(IF(H7>100000,IF((1-F7)>0.93,H7*0.025,0)),IF(H7>100000,IF((1-F7)>0.87,H7*0.125,0)),IF(H7>100000,IF((1-F7)<0.87,0),1))

What I want to acheive is
if F7 is greater than 93%, .25% of H7
if F7 is greater than 87%, .125% of H7
if F7 is less than 87, 0

Thanks so much to anybody who can help me out!

**tigeravatar** · 03-08-2013, 02:43 PM

soniarog,

Welcome to the forum!
Give this a try:

Please Login or Register  to view this content.

**martindwilson** · 03-08-2013, 02:49 PM

your boundaries are not clear what happens if f7=87%?
try
=h7*LOOKUP(F7,{0,0.88,0.92},{0,0.125,0.25})

**soniarog** · 03-08-2013, 03:59 PM

Thanks for your help. I'm still having trouble. I still need if H7 is greater than 100,000. Can you look at the data below and have a look?

D E F G H J
New Terminations Termination Net New Billed Retention
Business % Business Premium Bonus
65,929.80 39,240.96 3.83% 26,688.84 1,024,455.39 -
10,926.00 21,273.36 7.26% -10,347.36 293,043.36 -
1,368.60 592.44 1.03% 776.16 57,271.94 -
24,155.04 11,208.36 8.35% 12,946.68 134,273.48 -
23,523.12 - 0.00% 23,523.12 150,097.67 -