| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drag and fill on a filtered data sheet.
Hi,
I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. Price 1 100$ 10 260$ 40 300$ 55 40$ 120 170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Drag and fill on a filtered data sheet.
try this
=SUBTOTAL(5,A1:A100) On Oct 20, 5:44*pm, Pash wrote: Hi, I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. * Price * 1 * * * * * * *100$ * 10 * * * * * *260$ 40 * * * * * *300$ 55 * * * * * *40$ 120 * * * * *170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Drag and fill on a filtered data sheet.
Dear Muddan Madhu,
Thank you very much for your help, but I still can't get how to give the needed range. Let me explain once more what I need, I guess I wasn't clear first time. (filtered mode) Row No. Price 3rd column 1 100$ ? 10 260$ 40 300$ 55 40$ 120 170$ So we have already filtered sheet here, and I need to place a formula in the third column (instead "?") which will find the minimum value for Rows from 1 to 10, including filtered (hidden) rows. If I specify the range manually, I will not be able to drag and fill the formula so as to find out the minimum value for Rows from 10 to 40, from 40 to 55 etc., as the step is different (i. e. there is a different number of hidden rows between visible rows). I'm not sure I could make myself clear enough this time, but in any case, thank you very much for your help. "muddan madhu" wrote: try this =SUBTOTAL(5,A1:A100) On Oct 20, 5:44 pm, Pash wrote: Hi, I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. Price 1 100$ 10 260$ 40 300$ 55 40$ 120 170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Drag and fill on a filtered data sheet.
try this
for Row 1 to 40 =Small(B1:B40,1) or Min(B1:B40) for Row 41 to 55 =Small(B41:B55,1)........ On Oct 20, 6:54*pm, Pash wrote: Dear Muddan Madhu, Thank you very much for your help, but I still can't get how to give the needed range. Let me explain once more what I need, I guess I wasn't clear first time. (filtered mode) Row No. * Price * * 3rd column *1 * * * * * * *100$ * * * * *? *10 * * * * * *260$ * * * * *40 * * * * * *300$ * * * * *55 * * * * * *40$ * * * * * *120 * * * * *170$ So we have already filtered sheet here, and I need to place a formula in the third column (instead "?") which will find the minimum value for Rows from 1 to 10, including filtered (hidden) rows. If I specify the range manually, I will not be able to drag and fill the formula so as to find out the minimum value for Rows from 10 to 40, from 40 to 55 etc., as the step is different (i. e. there is a different number of hidden rows between visible rows). I'm not sure I could make myself clear enough this time, but in any case, thank you very much for your help. "muddan madhu" wrote: try this =SUBTOTAL(5,A1:A100) On Oct 20, 5:44 pm, Pash wrote: Hi, I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. * Price * 1 * * * * * * *100$ * 10 * * * * * *260$ 40 * * * * * *300$ 55 * * * * * *40$ 120 * * * * *170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance.- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Drag and fill on a filtered data sheet.
What is the logic you have applied for filtering I.e. what is the filtering
criteria -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Pash" wrote in message ... Hi, I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. Price 1 100$ 10 260$ 40 300$ 55 40$ 120 170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Drag and fill on a filtered data sheet.
Dear Ashish Mathur,
The column shown in the example is not the one to which filtering is applied. I've filtered the sheet by one column, but need the minimum values to be calculated for another one. Best wishes. "Ashish Mathur" wrote: What is the logic you have applied for filtering I.e. what is the filtering criteria -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Pash" wrote in message ... Hi, I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. Price 1 100$ 10 260$ 40 300$ 55 40$ 120 170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Drag and fill on a filtered data sheet.
Hi,
How are you? Could you mail me the workbook at -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Pash" wrote in message ... Dear Ashish Mathur, The column shown in the example is not the one to which filtering is applied. I've filtered the sheet by one column, but need the minimum values to be calculated for another one. Best wishes. "Ashish Mathur" wrote: What is the logic you have applied for filtering I.e. what is the filtering criteria -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Pash" wrote in message ... Hi, I need to find the minimum value among the cells between filtered rows, but the problem is the range between the filtered rows is different. Here's an example: (Already filtered mode) Row No. Price 1 100$ 10 260$ 40 300$ 55 40$ 120 170$ So I need the minimum price between rows No. 1 and 10 to be displayed on the first row, and the minimum price between rows No. 10 and 40 to be displayed on the 10th row etc. Could anybody help me on this issue? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using fill handle on filtered data | New Users to Excel | |||
| Filter data and fill only filtered data | Excel Discussion (Misc queries) | |||
| Updated filtered data on next sheet | Excel Discussion (Misc queries) | |||
| Help to sort out filtered data from the data contained in another sheet of the same workbook | Excel Worksheet Functions | |||
| Does Excel support Auto fill on filtered data? | Excel Discussion (Misc queries) | |||
Linear Mode
