Excel nested IF formula question

I have a question regarding Nested If statements in Excel 2003. I have
a commission structure below that needs to be nested
0-$50K = 10%
50-100K = 20%
100-150K = 25%
150-200K = 30%
200K+ - 35%

For example:
Fee                 commission rate              commission
19,125                10%                            1,912.50
8,000                 10%                                 800.00
8,000                 10%                                 800.00

8,000                 10%                                 800.00
Total Billings: 43,125

Here is where I am running into a problem. At this point the person has
reached a total billing of $43,125  with the next commission it needs
to be split between the 10% rate and the 20% rate as follows:
Fee                    commission rate
        commission
8,000                 10% of the first 6,875 (to get to 50,000)
     687.50
                          20% of the next 1,125 (the dollar over
50,000)    225.00

8,000                 20%
               1,600
This split will happen at each new level reached and I'm failing to
figure out an easy way to handle these events. Is there an easy way to
add this into the IF statement:
=IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
IF(H3>=50000,D3*20%,D3*10%))))

In the above H3 is the Total Billings and D3 is the fee that the
commission is paid on. In the above it gives the below:
Fee                 commission rate              commission
19,125                10%                               1,912.50
8,000                 10%                                 800.00
8,000                 10%                                 800.00

8,000                 10%                                 800.00
8,000                 20%                                1600.00 this
should be the 6875 @10% and 1,125 @20% shown above for a total of
$912.50
8,000                 20%                                1600.00

I hope this makes sense. Any simple Excel trick I can add in here to
make this work smoothly would be greatly appreciate!!

Thanks in advance,
D

0
hanesds (3)
11/8/2005 7:54:10 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
422 Views

Similar Articles

[PageSpeed] 58

Hi D

See J.E page
http://www.mcgimpsey.com/excel/variablerate.html


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


<hanesds@hotmail.com> wrote in message news:1131479650.376813.303650@g43g2000cwa.googlegroups.com...
>I have a question regarding Nested If statements in Excel 2003. I have
> a commission structure below that needs to be nested
> 0-$50K = 10%
> 50-100K = 20%
> 100-150K = 25%
> 150-200K = 30%
> 200K+ - 35%
>
> For example:
> Fee                 commission rate              commission
> 19,125                10%                            1,912.50
> 8,000                 10%                                 800.00
> 8,000                 10%                                 800.00
>
> 8,000                 10%                                 800.00
> Total Billings: 43,125
>
> Here is where I am running into a problem. At this point the person has
> reached a total billing of $43,125  with the next commission it needs
> to be split between the 10% rate and the 20% rate as follows:
> Fee                    commission rate
>        commission
> 8,000                 10% of the first 6,875 (to get to 50,000)
>     687.50
>                          20% of the next 1,125 (the dollar over
> 50,000)    225.00
>
> 8,000                 20%
>               1,600
> This split will happen at each new level reached and I'm failing to
> figure out an easy way to handle these events. Is there an easy way to
> add this into the IF statement:
> =IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
> IF(H3>=50000,D3*20%,D3*10%))))
>
> In the above H3 is the Total Billings and D3 is the fee that the
> commission is paid on. In the above it gives the below:
> Fee                 commission rate              commission
> 19,125                10%                               1,912.50
> 8,000                 10%                                 800.00
> 8,000                 10%                                 800.00
>
> 8,000                 10%                                 800.00
> 8,000                 20%                                1600.00 this
> should be the 6875 @10% and 1,125 @20% shown above for a total of
> $912.50
> 8,000                 20%                                1600.00
>
> I hope this makes sense. Any simple Excel trick I can add in here to
> make this work smoothly would be greatly appreciate!!
>
> Thanks in advance,
> D
> 


0
rondebruin (3790)
11/8/2005 8:03:49 PM
I believe John McGimpsey has created a web page for just your situation:

http://www.mcgimpsey.com/excel/variablerate.html

-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


<hanesds@hotmail.com> wrote in message
news:1131479650.376813.303650@g43g2000cwa.googlegroups.com...
> I have a question regarding Nested If statements in Excel 2003. I have
> a commission structure below that needs to be nested
> 0-$50K = 10%
> 50-100K = 20%
> 100-150K = 25%
> 150-200K = 30%
> 200K+ - 35%
>
> For example:
> Fee                 commission rate              commission
> 19,125                10%                            1,912.50
> 8,000                 10%                                 800.00
> 8,000                 10%                                 800.00
>
> 8,000                 10%                                 800.00
> Total Billings: 43,125
>
> Here is where I am running into a problem. At this point the person has
> reached a total billing of $43,125  with the next commission it needs
> to be split between the 10% rate and the 20% rate as follows:
> Fee                    commission rate
>         commission
> 8,000                 10% of the first 6,875 (to get to 50,000)
>      687.50
>                           20% of the next 1,125 (the dollar over
> 50,000)    225.00
>
> 8,000                 20%
>                1,600
> This split will happen at each new level reached and I'm failing to
> figure out an easy way to handle these events. Is there an easy way to
> add this into the IF statement:
> =IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
> IF(H3>=50000,D3*20%,D3*10%))))
>
> In the above H3 is the Total Billings and D3 is the fee that the
> commission is paid on. In the above it gives the below:
> Fee                 commission rate              commission
> 19,125                10%                               1,912.50
> 8,000                 10%                                 800.00
> 8,000                 10%                                 800.00
>
> 8,000                 10%                                 800.00
> 8,000                 20%                                1600.00 this
> should be the 6875 @10% and 1,125 @20% shown above for a total of
> $912.50
> 8,000                 20%                                1600.00
>
> I hope this makes sense. Any simple Excel trick I can add in here to
> make this work smoothly would be greatly appreciate!!
>
> Thanks in advance,
> D
>

0
ragdyer1 (4060)
11/8/2005 8:05:36 PM
=D3*10%+MAX(0,D3-50000)*10%+MAX(0,D3-100000)*5%+MAX(0,D3-150000)*5%+MAX(0,D3
-200000)*5%

gives you the commission, but I don't see how this differs from your nested
IF. If you want to calculate the additional commission, just subtract the
commission to date.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


<hanesds@hotmail.com> wrote in message
news:1131479650.376813.303650@g43g2000cwa.googlegroups.com...
> I have a question regarding Nested If statements in Excel 2003. I have
> a commission structure below that needs to be nested
> 0-$50K = 10%
> 50-100K = 20%
> 100-150K = 25%
> 150-200K = 30%
> 200K+ - 35%
>
> For example:
> Fee                 commission rate              commission
> 19,125                10%                            1,912.50
> 8,000                 10%                                 800.00
> 8,000                 10%                                 800.00
>
> 8,000                 10%                                 800.00
> Total Billings: 43,125
>
> Here is where I am running into a problem. At this point the person has
> reached a total billing of $43,125  with the next commission it needs
> to be split between the 10% rate and the 20% rate as follows:
> Fee                    commission rate
>         commission
> 8,000                 10% of the first 6,875 (to get to 50,000)
>      687.50
>                           20% of the next 1,125 (the dollar over
> 50,000)    225.00
>
> 8,000                 20%
>                1,600
> This split will happen at each new level reached and I'm failing to
> figure out an easy way to handle these events. Is there an easy way to
> add this into the IF statement:
> =IF(H3>=200000,D3*10%,IF(H3>=150000,D3*30%,IF(H3>=100000,D3*25%,
> IF(H3>=50000,D3*20%,D3*10%))))
>
> In the above H3 is the Total Billings and D3 is the fee that the
> commission is paid on. In the above it gives the below:
> Fee                 commission rate              commission
> 19,125                10%                               1,912.50
> 8,000                 10%                                 800.00
> 8,000                 10%                                 800.00
>
> 8,000                 10%                                 800.00
> 8,000                 20%                                1600.00 this
> should be the 6875 @10% and 1,125 @20% shown above for a total of
> $912.50
> 8,000                 20%                                1600.00
>
> I hope this makes sense. Any simple Excel trick I can add in here to
> make this work smoothly would be greatly appreciate!!
>
> Thanks in advance,
> D
>


0
bob.phillips1 (6510)
11/8/2005 8:18:33 PM
Thank you so much for the help. It worked - well sort of. I came up
with the following:
=SUMPRODUCT(--(H8>$J$3:$J$7),(H8-$J$3:$J$7), $L$3:$L7)

the only problem is when I try to drag that formula I get the #VALUE
error because it changes to
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L8)

When I need it to ACTUALLY be:
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L7) not to receive
the error - any ideas on why it changes the last value from $L7 to $L8?
and how to keep it from doing that so it will function properly?

Thanks again,

D
Ron de Bruin wrote:
> Hi D
>
> See J.E page
> http://www.mcgimpsey.com/excel/variablerate.html
> 
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl

0
hanesds (3)
11/9/2005 11:14:27 PM
Thank you so much for the help. It worked - well sort of. I came up
with the following:
=SUMPRODUCT(--(H8>$J$3:$J$7),(H8-$J$3:$J$7), $L$3:$L7)

the only problem is when I try to drag that formula I get the #VALUE
error because it changes to
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L8)

When I need it to ACTUALLY be:
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L7) not to receive
the error - any ideas on why it changes the last value from $L7 to $L8?
and how to keep it from doing that so it will function properly?

Thanks again,

D

0
hanesds (3)
11/9/2005 11:14:51 PM
Your L7 cell reference is in mixed form.

To copy it down rows without changing, just revise it to an absolute
reference:
$L$7

-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

<hanesds@hotmail.com> wrote in message
news:1131578091.462638.317530@o13g2000cwo.googlegroups.com...
Thank you so much for the help. It worked - well sort of. I came up
with the following:
=SUMPRODUCT(--(H8>$J$3:$J$7),(H8-$J$3:$J$7), $L$3:$L7)

the only problem is when I try to drag that formula I get the #VALUE
error because it changes to
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L8)

When I need it to ACTUALLY be:
=SUMPRODUCT(--(H9>$J$3:$J$7),(H9-$J$3:$J$7), $L$3:$L7) not to receive
the error - any ideas on why it changes the last value from $L7 to $L8?
and how to keep it from doing that so it will function properly?

Thanks again,

D


0
ragdyer1 (4060)
11/10/2005 5:11:03 PM
Reply:

Similar Artilces:

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Cannot open Excel attachment from e-mail when Excel is already ope
This is driving me crazy. If I already have Excel open (with or with out a spreadsheet open) I cannot open an Excel spreadsheet attached to an e-mail. If I close Excel and retry it will open Excel and the spreadsheet. Any ideas? I hate having to close all my open spreadsheets when I want to open a spreadsheet that's been e-mailed to me! One of these usually works with a similar problem starting workbooks by double clicking them in Windows Explorer. Maybe one will work for you and your email problem. Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close E...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Concatenation question
I am trying to concatenate two fields and then add text in brackets to the end. Here is what I have: StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)") I want the end result to be: Smith, Mary (Mgr) How do I achieve this? Thank you, Terri What you've done will work. You can also use the "+" operator if there is a possibility that there will be a missing first name: StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)") That will avoid an extra spa...

Some activity customization questions
User came to me asking about some improvements... 1. On the Contacts Activities Tab they would like to see the date the activity was created. 2. For an Activity they would like to change the default duration. I figured these should be easy in the customization area of CRM but dont see away to customize activities forms. Am I missing something? Activities forms are not customizable. Sal "Keith Franklin" <ka_franklin@nospam.empowered.com> wrote in message news:u1$JLEaRDHA.2148@TK2MSFTNGP12.phx.gbl... > User came to me asking about some improvements... > > 1. On t...

Exchange & DNS Question
I have 2 domains in our network as follows: domain 1 = adomain.com mail = larry (Exchange 5.5 on W2K server) DC = curly (Windows 2003 server) domain 2= bdomain.com , bdomain.local mail = sleepy (Exchange 2000 on W2K server) DC = grumpy (W2K server) Users from bdomain.com can send emails to adomain.com but emails from adomain.com does not get to bdomain.com. We receive "host unknown" error message. Each domain receives all other emails without any problem. I am able to do forward and reverse DNS lookups of the other domain from each domain. Any help would be greatly appreciated...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Starting an Office Program (word, excel) in Outlook to send email
I'm taking an Outlook 2002 class. The latest assignment requires I open a Microsoft app in Outlook to send an email. Troubleshooting so far: Have tried to install some addl features from CD, restarted the pc (O/S WINXP) but still get error "Cannot complete action". Get no reason why Outlook cannot perform this action and the HELP hasn't only points me to inserting an object or some variation thereof. My Outlook is configured to use with AOL 9.0. Steps (abbreviated) given in textbook to perform: Actions New mail using Microsoft Office Also tried to perform from Wo...

Complex question about importing, analyzing data
I get a cvs file emailed to me. I am trying hard to figure out how to import the data into Excel not in whole, but only selected columns. Once that is done, I would like to run a formula such as subract d from 1 row from e from another row if and only if A is the same. Could someone point me to a book or get me started and I will try to wrestle with the rest? What I would do is import the whole file into a Blank Worksheet - then delete what you don't want and then copy and past what you do want into your "Master Sheet" As for the formula something like the following should ...

is there a way to program my Excel file to do a loop?
Hi all, If I want B10 to B17 all follow the change of the same number(copy cell), let's say I put it in A1, and C10 follows the change of A2(copy cell), and C11 follows the change of A3(copy cell), and C12 follows the change of A4(copy cell), then I have 4 variables in my calculations: A1, A2, A3, A4. I want to loop each of the variables in a different set, then I hope the whole worksheet will be able to refresh following the change of A1, A2, A3, A4, and then I want to find the very set of A1, A2, A3, A4 that gives the smallest value of D10, how do I program the whole procedure...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Excel Grid Lines in Outlook Preview Pane
Is there any way to send a spreadsheet to a mail recipient in the body of the message and not have the grid lines show up in the Outlook preview pane when the recipient views the e-mail? If you open the e-mail in Outlook, the grid lines disappear, but in the preview pane they are there. Using Excel and Outlook 2003. Uzytkownik "Kimberly" <Blackrose73@gmail.com> napisal w wiadomosci news:1143658600.180739.308310@v46g2000cwv.googlegroups.com... > Is there any way to send a spreadsheet to a mail recipient in the body > of the message and not have the grid lines show up ...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Excel error 1706
Whenever I try to start excel on my computer it displays an error message and it shuts itself down, any other program from the office pack works properly, except for the one I mentioned. I reinstalled it and it does the exact same thing. Thanks in advance. ...

when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is called Book1 and the other document is the actual document that I want to open. How do I stop this from happening? Brian To prevent Book1 from opening you can append /e to your shortcut for opening Excel. "C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e Alternative to the above............ Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel Worksheet. Then if running Win98 OS Edit> select "Open" and Edit. If using WinXP OS you would scroll down to...