ranking with 2 parameters of inverse relation
 

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.

ranking with 2 parameters of inverse relation
 

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
Singapore
---
"Eddy Stan" wrote:
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.

ranking with 2 parameters of inverse relation
 

"Max" wrote:
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)

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" wrote:
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
Singapore
---
"Eddy Stan" wrote:
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.

ranking with 2 parameters of inverse relation
 

On Jan 25, 9:16*am, Joe User <joeu2004 wrote:
"Max" wrote:
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)

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" wrote:
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
Singapore
---
"Eddy Stan" wrote:
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.- Hide quoted text -

- Show quoted text -

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