ranking with 2 parameters of inverse relation

  • Thread starter Thread starter Eddy Stan
  • Start date Start date
E

Eddy Stan

hi
how to find promising customer
Customer dues to pay are 5k and pays all bills in 30days
while customer B dues to pay are 500k pays in 32 days
i wish to say customer B is good as the though dues are 500k still manages
to pay in 32days.
how to put this as function please.
 
Think you could associate it via simply using: Amt divided by Num of Days
Assume Amts in B2 down, Num of Days in C2 down
In D2, copied down: =B2/C2
Then in E2, copied down: =RANK(D2,D$2:D$100)
will give you the required relative ranking
Adjust the range to suit. voila? hit the YES below
 
Max said:
Think you could associate it via simply using:
Amt divided by Num of Days Assume Amts in
B2 down, Num of Days in C2 down[.] In D2,
copied down: =B2/C2 Then in E2, copied down:
=RANK(D2,D$2:D$100)
Click to expand...

That was my initial thought, too. And that does seem to be the way that
"Eddy" wants to rank customers.

But then I wondered: why should a customer who pays 5k in 30 be ranked
"lower" than a customer who pays 50k in 299 days? Is that really what "Eddy"
wants?

(Where "lower rank" means higher rank number, according to Max's formula.
Set the third RANK parameter to 1 if you want "lower rank" to mean lower rank
number.)

So I thought: shouldn't they be ranked by their NPV?

But that leads to some results that might be contrary to the way that "Eddy"
wants it to be. For example, the 5k/30day customer is ranked higher (lower
RANK number) than the 500k/32day customer based on NPV.


----- original message -----
 
Max said:
Think you could associate it via simply using:
Amt divided by Num of Days Assume Amts in
B2 down, Num of Days in C2 down[.]  In D2,
copied down: =B2/C2 Then in E2, copied down:
=RANK(D2,D$2:D$100)
Click to expand...

That was my initial thought, too.  And that does seem to be the way that
"Eddy" wants to rank customers.

But then I wondered:  why should a customer who pays 5k in 30 be ranked
"lower" than a customer who pays 50k in 299 days?  Is that really what "Eddy"
wants?

(Where "lower rank" means higher rank number, according to Max's formula. 
Set the third RANK parameter to 1 if you want "lower rank" to mean lower rank
number.)

So I thought:  shouldn't they be ranked by their NPV?

But that leads to some results that might be contrary to the way that "Eddy"
wants it to be.  For example, the 5k/30day customer is ranked higher (lower
RANK number) than the 500k/32day customer based on NPV.

----- original message -----



Max said:
Think you could associate it via simply using: Amt divided by Num of Days
Assume Amts in B2 down, Num of Days in C2 down
In D2, copied down: =B2/C2
Then in E2, copied down: =RANK(D2,D$2:D$100)
will give you the required relative ranking
Adjust the range to suit. voila? hit the YES below
Click to expand...

- Show quoted text -
Click to expand...

how about we scale down the amt payable by using LN(), in that way,
suppose the following situations
a. 5k : 30days
b. 5.5k : 32days
c. 6k : 32 days
d. 500k : 32 days
e. 50k : 299days
=LN(amt)/days, the results will be
a. 0.0536
b. 0.0533
c. 0.0560
d. 0.1942
e. 0.0131
if u sort it by descending order
d. 500k : 32 days
c. 6k : 32 days
a. 5k : 30days
b. 5.5k : 32days
e. 50k : 299days

as such, a larger portion is allocated for the days of payment, while
still considering the amt payable
 
Back
Top