Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inverse Ranking | Excel Discussion (Misc queries) | |||
In relation to macros | Excel Discussion (Misc queries) | |||
how can i find the relation between x & y in chart | Charts and Charting in Excel | |||
Ranking on basis of 3 Parameters | Excel Worksheet Functions | |||
Ranking on basis of 3 Parameters | Excel Worksheet Functions |