• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting specific value from a table

Y

ydmani

New Member
Hi,

I have created a database table containing 3000 rows, with a "values to query" field in the left most column to get the filtered data (like using a number filter selecting the data which lies between specified limit, for example fetch only those rows/records with a minimum value of say 100000 and maximum value of 1100000. Now the problem is:-

1. This is not a calculated field, as I need number filter in this left most field "values to Query". I need a number filter and not a text filter.
2. I am getting data in this field from a helper column through 'VALUE' function (helper column is based on a calculated field).
3. Cells which do not have values return a "0" zero.
4. SUBTOTAL function with MAX Sub-function is working fine, but problem arises when I tried to fetch the smallest number from this field "Values to Query", as it returns a zero "0" as the smallest number.
5. SMALL function will not work as my this field is having large number of cells with zero value.
6. I have tried Excel option of "Do not show zero if a cell is having a zero value", but not getting the results.

Is there any way out through which I can extract the smallest value even after filtering the data (SUBTOTAL is necessary) and even if I am having so many cells with zero values. Please help

Thanks in advance

Mani

Sample file is attached
 

Attachments

  • File for forum.xlsx
    94.8 KB · Views: 5
1] Subtotal with MIN ignore zeros for filtered data,

In B1, enter array formula (confirm pressing with SHIFT+CTRL+ENTER 3 keystrokes together) :

=MIN(IF(SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1)),IF(A5:A3005>0,A5:A3005)))

2] Please see attached file.

Regards
Bosco
 

Attachments

  • SubtotalMin.xlsx
    96.1 KB · Views: 11
Last edited:
Do simple thing,
In sheet2 cell "B2" enter below formula & copy down.
=IF(A2="#VALUE!","""",A2)

In sheet1 cell "A5" enter below formula & copy down.
=IFERROR(VALUE(Sheet2!B2),"")

In "B1" you will get cheapest rate i.e. 1029400
 
AVK said:
Do simple thing,
In sheet2 cell "B2" enter below formula & copy down.
=IF(A2="#VALUE!","""",A2)

In sheet1 cell "A5" enter below formula & copy down.
=IFERROR(VALUE(Sheet2!B2),"")

In "B1" you will get cheapest rate i.e. 1029400
Click to expand...

Is one formula not simpler than 6010 formula's ?
 
bosco_yip said:
1] Subtotal with MIN ignore zeros for filtered data,

In B1, enter array formula (confirm pressing with SHIFT+CTRL+ENTER 3 keystrokes together) :

=MIN(IF(SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1)),IF(A5:A3005>0,A5:A3005)))
2] Please see attached file.

Regards
Bosco
Click to expand...


Thanks a ton Bosco.
Your formula is superb and working absolutely fine. Thanks again
 
Derek McGill said:
Or try this if you will have any negative numbers :-
=SMALL(A5:A3005,COUNTIF(A5:A3005,0)+COUNTIF(A5:A3005,"<0")+1)
Click to expand...

Hi Derek,

Thanks for your response. I have tried your formula, it was good until or unless I do not filter the values. It does not work with the filtered values and continue to fetch record from the whole table field.
 
AVK said:
Do simple thing,
In sheet2 cell "B2" enter below formula & copy down.
=IF(A2="#VALUE!","""",A2)

In sheet1 cell "A5" enter below formula & copy down.
=IFERROR(VALUE(Sheet2!B2),"")

In "B1" you will get cheapest rate i.e. 1029400
Click to expand...


Hi AVK,

Thanks for your response.
I wanted a formula which can fetch me the smallest value from the table field every time I filter the data and Bosco's formula is perfect.
 
ydmani said:
Hi Bosco,

I was trying to understand the formula provided by you but finding it difficult.

Regards
Click to expand...
The structure is a 2 Conditionals MIN+IF formula.

1] {=MIN(IF(Condition1,IF(Condition2,Range)))}

becomes >>

{=MIN(IF(SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1)),IF(A5:A3005>0,A5:A3005)))}

2] Also, you can use this :

{=MIN(IF(Condition1*Condition2,Range))}

becomes >>

{=MIN(IF(SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1))*(A5:A3005>0),A5:A3005))}

3] And, if you have Excel 2013, you can use this non-array formula instead.

=AGGREGATE(15,6,A5:A3005/(A5:A3005>0)/SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1)),1)

Edit :

1] Formula [1] and [2] look alike, but formula [1] will run faster than [2]. Because IF function is faster than mathematical calculation.

2] Formula [3] also run faster than [1]. Because Aggregate function is a new function in replace of MIN+IF.

Regards
Bosco
 
Last edited:
bosco_yip said:
The structure is a 2 Conditionals MIN+IF formula.

1] {=MIN(IF(Condition1,IF(Condition2,Range)))}

becomes >>

{=MIN(IF(SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1)),IF(A5:A3005>0,A5:A3005)))}

2] Also, you can use this :

{=MIN(IF(Condition1*Condition2,Range))}

becomes >>

{=MIN(IF(SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1))*(A5:A3005>0),A5:A3005))}

3] And, if you have Excel 2013, you can use this non-array formula instead.

=AGGREGATE(15,6,A5:A3005/(A5:A3005>0)/SUBTOTAL(103,OFFSET(A5:A3005,ROW(A5:A3005)-ROW(A5),0,1)),1)

Edit :

1] Formula [1] and [2] look alike, but formula [1] will run faster than [2]. Because IF function is faster than mathematical calculation.

2] Formula [3] also run faster than [1]. Because Aggregate function is a new function in replace of MIN+IF.

Regards
Bosco
Click to expand...

Thanks Bosco for elaborating formula for us.

Regards
Mani
 
You must log in or register to reply here.
Back
Top