Help Structuring a Formula, Please!

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))))
 
Upvote 0
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? :biggrin:
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top