Help Structuring a Formula, Please!

[imported_unknown]

I've got a number in the excel sheet I'm working on right now that I'd like to try to calc in less than a billion different steps. Can anyone lend a hand?

I have a value that'll live in E16, to which I have to apply 7 different versions of the following:

If E16 is between:
13601 to 18360, then add 2,992 + 26% of E16's value in excess of 13600

18361 to 23130, then add 4,230 + 30% of E16's value in excess of 18360

etc.

etc.

So is there a way I can combine/nest about 7 of these in one cell without doing tons of separate calcs elsewhere and resolving this somewhat manually?

Thanks,

Jim

 

[IML]

If your table as shown occupies G1:I2, try using
=INDEX($G$1:$I$2,MATCH(E16,$G$1:$G$2),2)+INDEX($G$1:$I$2,MATCH(E16,$G$1:$G$2),3)*(E16-(INDEX($G$1:$I$2,MATCH(E16,$G$1:$G$2),1)-1))

I adjusted column I in the table to be true percents (ie .10 not 10). You'll get an N/A error if E1 is less than the lowest value in your table. The table can be expanded, but must the reference must be changes and column G must be sorted lowest to highest.

 

[Aladin Akyurek]

On 2002-10-02 13:07, mrjimmy wrote:
I've got a number in the excel sheet I'm working on right now that I'd like to try to calc in less than a billion different steps. Can anyone lend a hand?

I have a value that'll live in E16, to which I have to apply 7 different versions of the following:

If E16 is between:
13601 to 18360, then add 2,992 + 26% of E16's value in excess of 13600

18361 to 23130, then add 4,230 + 30% of E16's value in excess of 18360

etc.

etc.

So is there a way I can combine/nest about 7 of these in one cell without doing tons of separate calcs elsewhere and resolving this somewhat manually?

Thanks,

Jim

What is the result if E16 houses 14567?

 

[IML]

On 2002-10-02 13:42, Aladin Akyurek wrote:

What is the result if E16 houses 14567?

I hope 3243.42!

 

[imported_unknown]

Ok. So it looks like most of my problem was due to some lousy documentation that I have on using IF. Thanks for all your advice - here's the *blazingly* obvious and simple way I solved this:

=IF(E16>=37411,((E16-37410)*.46)+11087,IF(E16>=32651, ((E16-32650)*.42)+9088,IF(E16>=27891, ((E16-27890)*.38)+7279,IF(E16>=23131, ((E16-23103)*.34)+5661,IF(E16>=18361, ((E16-18360)*.30)+4230,IF(E16>=13601, ((E16-13600)*.26)+2992,IF(E16<=13600, (E16*.22))))))))

-jim

 

[IML]

On 2002-10-02 16:00, mrjimmy wrote:
Ok. So it looks like most of my problem was due to some lousy documentation that I have on using IF. Thanks for all your advice - here's the *blazingly* obvious and simple way I solved this:

=IF(E16>=37411,((E16-37410)*.46)+11087,IF(E16>=32651, ((E16-32650)*.42)+9088,IF(E16>=27891, ((E16-27890)*.38)+7279,IF(E16>=23131, ((E16-23103)*.34)+5661,IF(E16>=18361, ((E16-18360)*.30)+4230,IF(E16>=13601, ((E16-13600)*.26)+2992,IF(E16<=13600, (E16*.22))))))))

-jim

The benefit to setting up a table like this:
{0,0,0.22;13601,2992,0.26;18361,4230,0.3;23131,5661,0.34;27891,7279,0.38;32651,9088,0.42;37411,11087,0.46}
in G1:I7
and using the formula
=INDEX($G$1:$I$7,MATCH(E16,$G$1:$G$7),2)+INDEX($G$1:$I$7,MATCH(E16,$G$1:$G$7),3)*(E16-(MAX(0,(INDEX($G$1:$I$7,MATCH(E16,$G$1:$G$7),1)-1))))

would be it is easy to update info if it changes. (note what I believe to a type in bold in your formula above). As long as it works for you that great, but having information in one place has its advantages.

 

[Aladin Akyurek]

The IF(E16>=23131, ((E16-23103)*.34)+5661 subpart appears to me a bit strange. Any comments on this?

By the way, you can also use VLOOKUP accompanied with a table. This would give you a shorter formula and a better manegable set up.

 

[IML]

By the way, you can also use VLOOKUP accompanied with a table. This would give you a shorter formula and a better manegable set up.

DOH! Vlookup is much better. Here is mine modified for vlookup and assuming a named range
=VLOOKUP(E16,myrange,2)+(VLOOKUP(E16,myrange,3)*(E16-MAX(0,(VLOOKUP(E16,myrange,1)-1))))

 

[Aladin Akyurek]

On 2002-10-02 16:34, IML wrote:

By the way, you can also use VLOOKUP accompanied with a table. This would give you a shorter formula and a better manegable set up.

DOH! Vlookup is much better. Here is mine modified for vlookup and assuming a named range
=VLOOKUP(E16,myrange,2)+(VLOOKUP(E16,myrange,3)*(E16-MAX(0,(VLOOKUP(E16,myrange,1)-1))))

Did you hack my computer?