Calculate On Sliding Scale

    Hi everyone :


    I need your help , to calculate the intensives to be paid to a loan officer based on the number of cases he brings , according to the following :


    the first 10 cases 0
    from 11 to 15 cases 6 pounds
    from 16 to 20 cases 8 pounds
    from 21 cases to more 10 pounds


    so I need to put the total number of the cases , the loan officer brought in one cell, and this value to be processed and splitted to the segments above , and multiplied with adjacent pounds sums , and finally calculate the total of theintensive to be paid.


    I appologize for my poor english , as it is not my mother tongue :)

    Re: Splitting A Number Into Segments


    Hi triste,
    I'm sure there are better and shorter solutions.


    Say you have the Names of your loan officers in column A and their case numbers or so in column B - so in column A you could find the name(s) of loan officers multiple times, once for each case processed.


    Place this in C1
    =COUNTIF(A:A,"Name") - where Name will need to be replaced with the name of a Loan Officer. Copy the formula down and add all other respective Names.


    Place this in D1 (and copy down as needed as well).
    =IF(C1<11,0,IF(C1<=15,6,IF(C1<16,0,IF(C1<=20,8,IF(C1>20,10)))))


    Not sure about your calculation part.
    If one has, say 20 cases, will the payout be 20x8? If that, I'd add another cell to the right and do
    =Sum(C1xD1) (copy down once again)


    Stefan

    Re: Splitting A Number Into Segments


    thank you Stafen for your quick response


    in fact the workbook I'm using is designed to have only one row for each loan officer , and the whole thing should be processed in one cell which the total number of the cases will be put. ( I need a way to extract that total to these segments and doing the multiplying process and give me the final result)

    Re: Splitting A Number Into Segments


    I'm very sorry , but though Im registered , but the forum here is re-asking me to enter my user name and password , though it accepts them !!!!!!!!!!!, as a result I cant open the attachment

    Re: Splitting A Number Into Segments


    Hi Ian,
    I like your lookup option.

    Quote

    =LOOKUP(A2,B2:B5,D2:D5)*A2

    Nice and short - sweet.
    I'd done this a bit longer once again, so i liked your solution.
    =IF(A1<11,D2,IF(A1<=15,D3*A1,IF(A1<16,0,IF(A1<=20,D4*A1,IF(A1>20,D5*A1)))))


    I suppose that triste will have to change the reference from A2 to A1 in your formula in F2 unless s/he had a typo.

    Quote

    so I need that uumber in cell (a1) to be extracted acoording to that role and give me the final result


    Stefan

    Re: Splitting A Number Into Segments


    hi Ian :


    I DOWNLOADED THE FILE , IT IS PERFECT , THAT IS EXACTLY WHAT I NEED


    THANK YOU VERY MUCH , YOU REALLY DID A GREAT JOB TO ME , I REALLY APPRECIATE THAT :congrats: :congrats: :congrats:

    Re: Splitting A Number Into Segments


    triste


    Glad it worked.


    StefanG


    I like them short and sweet, apart from that I am still learning. Thank you


    Ian

    Re: Calculate On Sliding Scale


    hi Ian
    In the file attached , I need the number of cases(a1) to be segmented to the adjacent segments(c2 : c5) , then multiplied by the values (d2 : d5).


    I'm sorry that I can not find my thread to continue through it ,


    so what I need , not to multiply the whole number in one value depending on it's value , but to segment it to segments according to it's value , then make the multiplication process in one step.


    sorry for iany inconnvenience :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login