Excel formular help - min and max of all numbers but not zeros 1

[amandarose80]

I have a range of numbers that I want to find the min for and then the max for. But I want it to drop the zeros and take the numbers greater than 0.

I'm sure you would use an IF statement but I'm not sure how to get it to "drop" the zero values.

 

[DaleWatson123321]

Use of =DMIN and =DMAX (database functions) will work...

Here's are examples...
=DMIN(data,1,crit)
=DMAX(data,1,crit)

The reference to "data" is a range named "data". You can assign this range to ALL of your data - if, for example you already have a database set up. Appreciate that the "data" range can extend "beyond" the current range occupied by your data - for example to allow for the rows where you'll be adding data in the future.

The "database" functions require that each of the columns in your "data" range have a unique "field name".

For the field you want to use these functions on, you need to use that field name in what is called a "criteria" for the database functions.

I strongly recommend that the criteria for database functions be set up on a SEPARATE sheet. This is because Excel can "get confused" in some situations if the criteria is set up on the SAME database sheet. Additonally, it's "cleaner" to just have the database data on the database sheet.

In creating the criteria, enter the field name in one cell, and below that cell, enter: >0. Then assign the range name "crit" (short for "criteria&quot to BOTH the cells. You can of course use whatever name you prefer for the criteria range name, but of course be sure to use the same name in your formulas.

The "1" in the formulas occupies what is called the "offset" - or the column within your "data" (named range) from which you want to reference with your DMIN and DMAX functions. So, if your "data" range begins in Column A, and the column you want to reference is also Column A, you would use: 1. But if the column you want to reference is in column C, for example, then you would use: 3.

Another option instead of using a number for the offset, is to use the name of the field. But remember to place the field name in quotation marks. Example, if your field name is 'Sales', then you would use:

=DMIN(data,"Sales",crit)
=DMAX(data,"Sales",crit)

I hope this helps. If you encounter any difficulty, I can email you an example file I created based on your example.

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[amandarose80]

Unfortunately, you have lost me. Mainly on the field name part.
This is how the info is set up (There are 28 Agencies, A-AB)

Entity AgencyA AgencyB AgencyC ...MAX MIN
Indirect Cert. Costs 0 300 150 300 150
Visits 600 250 100 600 100
Indirect cost/visit 480 0 100 480 100

There are six entities.

Above is how we want the MAX and MIN to turn out in those situations.

 

[amandarose80]

This formula worked:
=MIN(IF(B21:BD21<>0,B21:BD21,&quot;&quot)
Press CTRL+Shift+Enter to make it an array formula.

 

[DaleWatson123321]

amanda,

It's obviously &quot;terrific&quot; that you resolved the task on your own. Here's a STAR in appreciation of you doing so, and sharing with others.

With your data having been &quot;reversed&quot; from the orientation I had referred to, it was no wonder you &quot;got lost&quot; with my explanation.

The orientation I had expected was as follows...
Agency IndirectCosts Visits IndirectCost/Visit
AgencyA 0 600 480
AgencyB 300 250 0
AgencyC 150 100 100

Using the above orientation, you could use the =DMIN and =DMAX database functions I mentioned.

As your application &quot;evolves&quot;, you might end up opting for this other orientation, because it will more easily allow for additional information - for example on the Type of Agency - i.e. Address, Region, Type, etc.

Then... If for example this information is based on a MONTHLY period, you could add a DATE column (field). And each month you would copy a &quot;Block of the 28 Agencies to a new set of rows, and enter a value for the NEXT MONTH in the DATE field.

Using Excel's database functions, you could generate a &quot;matrix&quot; (or Summary) of data - by Agency by Month, with a Month-Over-Month comparison, by Agency by Month by Region, etc.

I hope this gives you some &quot;food for thought&quot; about the possibilities of using Excel's database functions.

Because Microsoft has done a &quot;less-than-adequate&quot; job of explaining this POWERFUL component of Excel, I'm offering a &quot;collection&quot; of different example files I've created over the past couple years.

These examples will assist in getting anyone &quot;jump started&quot; and somewhat familiar with not only the &quot;database formulas&quot;, but also the &quot;Advanced Filter&quot; capabilities of Excel. Many Excel users do not realize that the criteria used for the &quot;database functions&quot; are set up in the same manner when using the &quot;Advanced Filter&quot;. So it's possible to use the database formulas for a &quot;Summary&quot; report, and use the same criteria for &quot;filtering&quot; the data for generation of &quot;detail&quot; reports - as &quot;back up&quot; for the Summary reports.

With the Advanced Filter and VBA, one can either &quot;filter-in-place&quot;, or extract data (a copy) to a &quot;separate&quot; sheet where the data can more easily and effectively be prepared for viewing and/or printing purposes.

As compared to other options in Excel, I would suggest a SIGNIFICANT benefit of the Advanced Filter component is that it's MUCH easier to specify whatever COMPLEX criteria is necessary to isolate the precise data required.

I hope this is of interest, and as mentioned, anyone who would like a copy of these example database files, please feel free to ask.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[amandarose80]

Thank you! After the day I've had I needed a star.