# 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)))))

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

triste

Post a copy of the layout of your sheet.

This will show us what we are dealing with.

Ian

• Re: Splitting A Number Into Segments

here is an example:

## Files

• Re: Splitting A Number Into Segments

triste

Have a look at the attached, I had to use column B for the list.

Regards

Ian

## Files

• 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

could you pls re-upload it to any other server

• Re: Splitting A Number Into Segments

Hi Ian,

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

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

• Re: Calculate On Sliding Scale

I'm sorry Dave , but I could not get benifit from these examples , it is so difficult for me

• Re: Calculate On Sliding Scale

I'm still trying but , could reach nothing , pls give me a hand to solve this problem

• Re: Calculate On Sliding Scale

Hi triste,
could you provide a before and after example maybe?
Stefan

## Participate now!

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