# Help Structuring a Formula, Please!

#### imported_unknown

##### Active Member
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

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.

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?

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

What is the result if E16 houses 14567?

I hope 3243.42!

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

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.

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.

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))))

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?

Replies
0
Views
181
Replies
5
Views
134
Replies
2
Views
792
Replies
33
Views
2K
Replies
2
Views
620

1,203,762
Messages
6,057,218
Members
444,914
Latest member
Mamun12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back