Filter function based on rows and columns

**dj_420** · 05-06-2012, 08:37 PM

Hi Guys,

I am a geologist from Australia, and almost a complete newbie on Excel.

I am just trying to create a filter function that will allow me to manipulate data based on both different row and column values.

The spreadsheet example I have attached shows the drillhole number (Hole_ID), from (Depth_From) and to (Depth_To) depths and metal values (Au_ppm and Cu_ppm).

What I want to create is a filter that says:

IF D2>D3 AND F2>5000

I want to be able to apply this filter to the entire spreadsheet and display the filtered results. I tried to put in the formula numerous times but I always seem to have the syntax incorrect.

This filter would tell me which drillholes finished in certain copper values (and probably should be drilled deeper).

On my example spreadsheet there should be two holes that finish in copper mineralisation greater than 5000ppm, these are BEN1081 and BEN1082. And I would like to be able to display these filtered results.

I am unsure if it is best to use filter function or another function to achieve these results?

Thanks very much for any help guys.

**FDibbins** · 05-06-2012, 08:55 PM

not sure if this is what you want, because based on your criteria, i get a different answer to you. take a look at the attached and let me know? i used conditional formatting (which you can remove if you want) to highlight "success", and i added a filter so that you can filter out (or in) what you want to see. was unsure what you wanted to show if success was met or not, so you can change my "comment" in the formula to suite ehat you want.

let me know if this works for you?

**dj_420** · 05-06-2012, 10:19 PM

Originally Posted by FDibbins

not sure if this is what you want, because based on your criteria, i get a different answer to you. take a look at the attached and let me know? i used conditional formatting (which you can remove if you want) to highlight "success", and i added a filter so that you can filter out (or in) what you want to see. was unsure what you wanted to show if success was met or not, so you can change my "comment" in the formula to suite ehat you want.

let me know if this works for you?

Hi FDibbins

That did work! Fantastic, the other drillhole wasn't highlighted only because you had >5000 in the formatting and the Cu number was 5000, if I change to >4999 will include that drillhole.

Thanks very much for your help, I'll be able to apply that to some real datasets and see how I go.

Cheers
dj_420

**dj_420** · 05-06-2012, 10:24 PM

Sorry FDibbins,

One other question. Where abouts do I go to edit the "Drill Deeper" and "Deep Enough" rules?

Cheers

**FDibbins** · 05-06-2012, 10:43 PM

if you look inthe cell where it sayds that, i put the formula in there. edit that part of the formula that is between " "'s you can put whatever you want between then...of if you just want 1 of them to remain, use "" note, there is no space between them this time

Shout if you need anything else?

edit: instead of >5000, you could alos use >=5000