Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a
problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
I am looking for a month with 69%
If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
I read that lookup will find "the largest value that is less than or equal to
the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
I read that lookup will find "the largest value that is less
than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Biff
Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Oops. Accidentally clicked on the "No" button for the "Helpful" question.
You have been helpful, I'm just not sure what will solve my problem. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
=INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0))
will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Oops! The second formula should of course start with
=IF(E1<MIN(C2:C20) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0)) will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Here's the slightly shorter array** version:
=INDEX(A2:A8,MATCH(MAX(IF(C2:C8<=E1,C2:C8)),C2:C8, 0)) The result is 7, matching 64.0 which is the largest value that is less than or equal to the lookup value. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... =INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0)) will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Peo
I'll give it a try! Thanks. tmarsh7407 "Peo Sjoblom" wrote: Oops! The second formula should of course start with =IF(E1<MIN(C2:C20) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0)) will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Biff
Got it. Thanks. tmarsh7407 "T. Valko" wrote: Here's the slightly shorter array** version: =INDEX(A2:A8,MATCH(MAX(IF(C2:C8<=E1,C2:C8)),C2:C8, 0)) The result is 7, matching 64.0 which is the largest value that is less than or equal to the lookup value. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... =INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0)) will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Peo
I ended up with the reference version. Once I saw what you were doing I was able to simplify, since i only care about the first time I hit the percent. Note my forecasting is done on a separate sheet from the pivot, hence the sheet names in the formula: =INDEX('Design Pivot VQ'!A$8:A$91,COUNTIF('Design Pivot VQ'!C$8:C$91,"<="&I14)+1,'Design Pivot VQ'!C$8:C$91,1) I then use the month number in VLOOKUP formulas in the later months of the forecast. Thank you for your help, feel free to use if anyone else has a similar question. tmarsh7407 "Peo Sjoblom" wrote: Oops! The second formula should of course start with =IF(E1<MIN(C2:C20) -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0)) will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup percentages
Biff
I ended up with the reference version. Once I saw what you were doing I was able to simplify, since i only care about the first time I hit the percent. Note my forecasting is done on a separate sheet from the pivot, hence the sheet names in the formula: =INDEX('Design Pivot VQ'!A$8:A$91,COUNTIF('Design Pivot VQ'!C$8:C$91,"<="&I14)+1,'Design Pivot VQ'!C$8:C$91,1) I then use the month number in VLOOKUP formulas in the later months of the forecast. Thank you for your help, feel free to use if anyone else has a similar question. tmarsh7407 "T. Valko" wrote: Here's the slightly shorter array** version: =INDEX(A2:A8,MATCH(MAX(IF(C2:C8<=E1,C2:C8)),C2:C8, 0)) The result is 7, matching 64.0 which is the largest value that is less than or equal to the lookup value. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... =INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2:C20," "&E1)+1),C2:C20,0)) will return the value in A2:A20 after finding the lower closest value in C2:C20 with the lookup value in E1 without the values being sorter in one way or the other, note that it will return an error if the lookup value is less than the smallest value. You can obviously fix that by using =IF(E1<MIN(B2:B20),"Not Found",INDEX(A2:A20,MATCH(LARGE(C2:C20,COUNTIF(C2: C20,""&E1)+1),C2:C20,0))) -- Regards, Peo Sjoblom "tmarsh7407" wrote in message ... Biff Thanks for the help. The more I think about it, the less I think LOOKUP is the right way to go. The percentage I am looking up is 69%, slightly less than the 69.5% in row 6, so I knew it would go back to 5, I was surprised to see that the data sometimes does not change from one month to another and can even have a negative payment (refund of an earlier overpayment). I had ruled out VLOOKUP because VLOOKUP requires the lookup column to be the leftmost column. Others I looked at like MATCH seem to require an exact match, not feasible when the pivot table can go from any percent to a much higher or even lower in a single month, or stay unchanged. I am hoping you or somebody knows of another function (or formula, or VB) that will find the closest lower percentage for my starting month and let me proceed from that point on. Thanks again. "T. Valko" wrote: I read that lookup will find "the largest value that is less than or equal to the lookup value", *If* the lookup array is sorted ascending which yours isn't. in this case month 5 with 35%. Why isn't the correct result month 7 with 64.0% ? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I read that lookup will find "the largest value that is less than or equal to the lookup value", in this case month 5 with 35%. I then have the percent catch up and use the table percentages for remaining forecast periods. If you know of some other function (or even some short VB code, emphasis on short) I would gladly consider it "T. Valko" wrote: I am looking for a month with 69% If there is not an exact match (as there is not in your sample) what result do you expect? -- Biff Microsoft Excel MVP "tmarsh7407" wrote in message ... I am fairly familiar with VLOOKUP and HLOOKUP, and pivot tables, but have a problem that perhaps somebody can help with. I have a pivot table built with three columns as in the following partial example (whole table is much longer but this should show the issue): A B C Month PayPercent CumPercent 1 24.1% 24.1% 2 0.0% 24.1% 3 10.9% 35.0% 4 0.0% 35.0% 5 0.0% 35.0% 6 34.5% 69.5% 7 -5.5% 64.0% I want to use CumPercent to find a starting point for projects that are in progress. I tried to use a vector form LOOKUP(projpct,$C$2:$C$84,$A$2:$A$84) to look for the nearest percent in Column C and then return the matching Month number from column A. Unfortunately it seems to give up and return 2 when I am looking for a month with 69% - I think it does not like seeing the same value twice and decides that it will not go any further. Even worse, the negative percent for month 7 means the column is not always in ascending order. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup percentages, return names | Excel Discussion (Misc queries) | |||
percentages | Charts and Charting in Excel | |||
Percentages | Excel Discussion (Misc queries) | |||
Percentages | Excel Worksheet Functions | |||
Percentages | Charts and Charting in Excel |