Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Ripon, Yorkshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula issues (XP)

    I'm new to Excel and to this forum so please bear with me. I say that because I'm having difficulty in even asking the question. Here goes...
    Basically I have 5 profit bands, and I am using the formula below to work out profits based on a certain number of new clients for each month. The problem is that if in a given month the number of clients falls between 2 price bands, how will it know to split some into 1 price band and the remainder into the next price band. Does that make sense [img]/forums/images/smilies/smile.gif[/img]

    =IF(SUM(B33)<=25,D3*(Prices!D6-Prices!C6)+C4,
    IF(SUM(B33)<=99,D3*(Prices!G6-Prices!F6)+C4,
    IF(SUM(B33)<=249,D3*(Prices!J6-Prices!I6)+C4,
    IF(SUM(B33)<=499,D3*(Prices!M6-Prices!L6)+C4,
    D3*(Prices!P6-Prices!O6)+C4))))

    So what this formula says is: If the number of clients (B33) is less than or equal to 25, then use these prices (Prices!D6-Prices!C6) and then add the clients from the previous month (C4), however If the number of clients (B33) is from 26 to 100, then use these prices (Prices!D6-Prices!C6) and then add the clients from the previous month (C4) etc.

    So far so good, but there are 2 problems:
    1) The price bands are 0-25, 26-99, 100-249, 250-499, 500+ and the formula is asking 0-25, 0-99, 0-249, 0-499. How can I change 'less than or equal to' to '26-99'?
    2)If in month 1 I have 20 clients, then in month 2 I have another 20. That's a total of 40 clients, of which 25 fall into the 0-25 price band and 15 fall into the 26-99 price band. How can I get the formula to make this distinction?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula issues (XP)

    1) you don't need to distinguish to remove the "lower bands".
    If it is <=25 it does the first one, to go to the second if, you are automatically >25 so the second is already >25 and <=99.

    Another way to do this to eliminate all the nested IFs is to use a VLOOKUP table
    For a discussion of some of these check out John Walkenbach's site

    2) I am not sure what you are after. If they sum is 40, how are you distinguishing the split of 25 and 15?
    Could you use SUMIF to distinguish between new an current customers and then use 2 calcs: the sumif "new" in the formula for the "new" and and the sumif of "Current" for the current. Then the total is the number you are after.

    If you need more details, coul you elaborate on your setup and how it works.

    Steve

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Location
    Ripon, Yorkshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula issues (XP)

    Thanks for the reply, but I am still none the wiser. I have tried many different ways of doing the formulas but can only ever get them to 1/2 work.
    I have attached a worksheet so you can see in more detail what I'm trying to do. In the top table are the prices and the commision earned per number of clients in the different bands. The lower table is where I want to be able to total the comissions. I think that I have got it cracked for month 1, however as soon as I add 75 or more clients to month 2 it all goes wrong [img]/forums/images/smilies/sad.gif[/img]
    Any help would be greatly appreciated, and I'd be happy to offer an incentive if it helps.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula issues (XP)

    One issue with your formula is that it all the references are "relative" so copying it does not work as it should. All the references to the items in row 1 and 2 should be absolute so it can copy more easily.

    I prefer the "lookup" route for a variety of reasons. The biggest is that you have 1 place for all the numbers. CHanging the table will thus change all the calculations without having to re-edit them all.

    I hav attached an example with (I think) your answers that you want. If it works (check out the numbers) I can go over the detials of how it works. If it doesn't work, we will have to troubleshoot the logic. It would help if you gave some numbers and the numbers you expect for them. You example only had a few of them and I was not sure if they were correct.

    Steve

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Location
    Ripon, Yorkshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula issues (XP)

    I can't thank you enough for taking the time to help me out.
    I can't quite understand how the 'lookup' works, but it certainly is a lot neater.

    What you have done is basically correct however let's say you have 26 clients in month1, that should give 52.74 ((25*1.99)+(1*2.99)). Then if we have 1 in month 2 what we should have is 55.73 because we need to add month 1 and month 2.
    In your example it we have 26 clients in month1 and 1 client in month 2, then for month 2 there should be 2.99 commission as we are into the next price band - 26 + 1=27 of which 25 are at 1.99 and 2 are at 2.99.

    Does that make sense?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula issues (XP)

    You are very welcome.

    Aha, I did not realize that the "clients" in row 10 were listing the "new clients": I assumed they were the total clients.

    This is an "easy fix": You can keep the same setup, just put this formula into B11 (it is all 1 line):

    =VLOOKUP(SUM($B$10:B10),$A$1:$C$6,3) + (SUM($B$10:B10) - VLOOKUP(SUM($B$10:B10),$A$1:$C$6,1)+1) * VLOOKUP(SUM($B$10:B10),$A$1:$C$6,2)

    Then copy B11 to C11:M11 (you will have to change the formatting as desired to darken the 6/12.

    The formula replaces the value in the col and row 10 (in the formula 4 times) with the running sum from col B to the current column.

    Is this what you are after?

    Steve
    A suggestion:
    The formula I have given you is composed of "essentially" 4 parts. I think if you break out the 4 parts and see the numbers, it might give you a better idea of what it is doing. In essence it "looks up" the number of clients in the table in rows1-6 and gets the item from the "row" that "matches".

    Yes it is "neater" than the nested ifs, it is also easier to change the numbers in case the limits or values change.

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Ripon, Yorkshire, England
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula issues (XP)

    Steve,

    Thanks again, that worked a treat!! I'm a very happy man. My final table ended up fairly large as I had to apply this to 5 products.

    If there is any way that I might be able to return the favour please let me know. I'm pretty handy with windows (95-XP & NT-2000 Srv), Linux/Red Hat, networking and web dev.

    Tom [img]/forums/images/smilies/smile.gif[/img]

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula issues (XP)

    You are welcome, again.

    If you want to return the favor, you can try browsing some of the other boards here and help answer any questions you have knowledge of. Share the knowledge.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •