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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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
Top