Error in this Excel Formula??

[killinggameshow]

I get an error on this formula, and I have no idea why

=IF(o50 < 192, 0, if(and(o50 > 191.99, o50 < 336), product(sum(o50, - 192), .023), if(and(o50 > 335.99, o50 < 479), sum(3, product(sum(o50, -336), .031)), if(and(o50 > 478.99, o50 < 623), sum(8, product(sum(o50, -479), .04)), if(and(o50 > 622.99, o50 < 767), sum(14, product(sum(o50, -623), .049)), if(and(o50 > 766.99, o50 < 911), sum(21, product(sum(o50, -767), .057)), sum(29, product(sum(o50, -911), .065))))))))


It highlights the SUM(o50, 767) for the error. Any clues?

 

[Zathras]

I think you have run into a limit of nesting levels that Excel can support. You have nested IF functions inside of IF functions and a PRODUCT function inside of IF functions and a SUM function inside of the PRODUCT function. There are a couple of things you can do:

1. Change the formula to look like this (instead of using SUM and PRODUCT functions:

=IF(O50<192,0,IF(O50<336,0.023*(O50-192),IF(O50<479,3+0.031*(O50-336),IF(O50<767,14+0.049*(O50-623),IF(O50<911,21+0.057*(O50-767),29+0.065*(O50-911))))))

Or 2. Change the methodology and use a table-driven approach with VLOOKUP:
Put these values in the cells indicated:

A1: 0
B1: 0
C1: 0
A2: 192
B2: 0
C2: 0.023
A3: 336
B3: 3
C3: 0.031
A4: 479
B4: 8
C4: 0.040
A5: 623
B5: 14
C5: 0.049
A6: 767
B6: 21
C6: 0.057
A7: 911
B7: 29 
C7: 0.065

Then use this formula:

   =VLOOKUP(O50,$A$1:$C$7,2,1)+VLOOKUP(O50,$A$1:$C$7,3,1)*(O50-VLOOKUP(O50,$A$1:$C$7,1,1))

Method 2 has the advantage that if you need to have more than 7 break points you can. (Excel chokes at that point.)

 

[Dreamboat]

What are you doing with O50, 767?

Why not just O50+767?



Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX