Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
Does anyone have any suggestions on how to determine the values under
following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
hi, Eric !
not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula: [D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) hth, hector. Does anyone have any suggestions on how to determine the values under following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
Under column A, 2,5,3,4,7,8,4,3,8,10
Under column C, 12,5,7,11,13,5,8,23,20,3 There is 50 in cell G1 For the first number in cell D1 Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1 and the sum from C1 to C6 50 in cell G1, then return highest number from A1 to A5 into cell D1, which is 7 For the next number in cell D2 Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1 and the sum from C2 to C8 50 in cell G1, then return highest number from A2 to A7 into cell D2, which is 8 .... Thank you very much for any suggestions Eric "Héctor Miguel" wrote: hi, Eric ! not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula: [D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) hth, hector. Does anyone have any suggestions on how to determine the values under following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
hi, Eric !
try eliminating the "plus one" from the final part of the formula: from: =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) to: =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),))),)) hth, hector. Under column A, 2,5,3,4,7,8,4,3,8,10 Under column C, 12,5,7,11,13,5,8,23,20,3 There is 50 in cell G1 For the first number in cell D1 Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1 and the sum from C1 to C6 50 in cell G1, then return highest number from A1 to A5 into cell D1, which is 7 For the next number in cell D2 Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1 and the sum from C2 to C8 50 in cell G1, then return highest number from A2 to A7 into cell D2, which is 8 not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula: [D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) Does anyone have any suggestions on how to determine the values under following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
Or maybe this formula filled down from D1 (entered normally):
=LOOKUP(G$1,SUBTOTAL({9,4},OFFSET(C1,,{0,-2},ROW(C1:C$10)-ROW()+1))) "Eric" wrote: Under column A, 2,5,3,4,7,8,4,3,8,10 Under column C, 12,5,7,11,13,5,8,23,20,3 There is 50 in cell G1 For the first number in cell D1 Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1 and the sum from C1 to C6 50 in cell G1, then return highest number from A1 to A5 into cell D1, which is 7 For the next number in cell D2 Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1 and the sum from C2 to C8 50 in cell G1, then return highest number from A2 to A7 into cell D2, which is 8 ... Thank you very much for any suggestions Eric "Héctor Miguel" wrote: hi, Eric ! not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula: [D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) hth, hector. Does anyone have any suggestions on how to determine the values under following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
Thank everyone very much for suggestions
Could you please tell me how to change the code in order to determine the minimum number under column A instead of maximum one? Regards Eric "Lori" wrote: Or maybe this formula filled down from D1 (entered normally): =LOOKUP(G$1,SUBTOTAL({9,4},OFFSET(C1,,{0,-2},ROW(C1:C$10)-ROW()+1))) "Eric" wrote: Under column A, 2,5,3,4,7,8,4,3,8,10 Under column C, 12,5,7,11,13,5,8,23,20,3 There is 50 in cell G1 For the first number in cell D1 Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1 and the sum from C1 to C6 50 in cell G1, then return highest number from A1 to A5 into cell D1, which is 7 For the next number in cell D2 Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1 and the sum from C2 to C8 50 in cell G1, then return highest number from A2 to A7 into cell D2, which is 8 ... Thank you very much for any suggestions Eric "Héctor Miguel" wrote: hi, Eric ! not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula: [D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) hth, hector. Does anyone have any suggestions on how to determine the values under following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine the values?
Hi Eric, for the min value change the 4 to 5 - see help on the subtotal
function. The logic of the formula is to create a two column array of running sums and associated max values from which to lookup the threshold value. "Eric" wrote: Thank everyone very much for suggestions Could you please tell me how to change the code in order to determine the minimum number under column A instead of maximum one? Regards Eric "Lori" wrote: Or maybe this formula filled down from D1 (entered normally): =LOOKUP(G$1,SUBTOTAL({9,4},OFFSET(C1,,{0,-2},ROW(C1:C$10)-ROW()+1))) "Eric" wrote: Under column A, 2,5,3,4,7,8,4,3,8,10 Under column C, 12,5,7,11,13,5,8,23,20,3 There is 50 in cell G1 For the first number in cell D1 Starting to sum from C1 to bottom, if the sum from C1 to C5 <= 50 in cell G1 and the sum from C1 to C6 50 in cell G1, then return highest number from A1 to A5 into cell D1, which is 7 For the next number in cell D2 Starting to sum from C2 to bottom, if the sum from C2 to C7 <= 50 in cell G1 and the sum from C2 to C8 50 in cell G1, then return highest number from A2 to A7 into cell D2, which is 8 ... Thank you very much for any suggestions Eric "Héctor Miguel" wrote: hi, Eric ! not sure if I'm following you accurately, but you could try as first approach w/ the following (CSE) array formula: [D1] =max(offset(a1,,,match($g$1,subtotal(9,offset(c1,, ,row(indirect("1:20")),)))+1,)) hth, hector. Does anyone have any suggestions on how to determine the values under following conditions? Under column A, there is a list of numbers. Under column C, there is a list of numbers. There is 145 in cell G1. Starting to sum from C1 to bottom, if the sum from C1 to C9 < 145 in cell G1 and the sum from C1 to C10 = 145 in cell G1, then return highest number from A1 to A10 into cell D1. For the next number Starting to sum from C2 to bottom, if the sum from C2 to C9 < 145 in cell G1 and the sum from C2 to C10 = 145 in cell G1, then return highest number from A2 to A10 into cell D2. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the values from a list? | Excel Worksheet Functions | |||
How to determine the values from a list? | Excel Discussion (Misc queries) | |||
How to determine the values from a list? | Excel Worksheet Functions | |||
I need to determine how frequently two values appear together | Excel Discussion (Misc queries) | |||
How to determine geometric mean of 152 values? | Excel Worksheet Functions |