Solved

excel formula

Posted on 2011-03-09
5
332 Views
Last Modified: 2012-05-11
Please can you help with the formula in sheet2 D11

I want to return the Max number within the 'filtered' dates (D5:D6)

So the result should equal 12


help.xls
0
Comment
Question by:Gazza83
  • 3
5 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35089926
I can't tell exactly what you are trying to do from the example but is you want to only look at filtered values then use the SUMPRODUCT function as described below.

The SUBTOTAL function provides various aggregate functions such as SUM and AVERAGE that ignore hidden rows in AutoFilter lists. The following parameter values only ignore rows hidden with the AutoFilter function and are compatible with all versions of Excel.

   1 - AVERAGE
   2 - COUNT
   3 - COUNTA
   4 - MAX
   5 - MIN
   6 - PRODUCT
   7 - STDEV
   8 - STDEVP
   9 - SUM
   10 - VAR
   11 - VARP

In Excel 2003 and later one of the features of the SUBTOTAL function is that it can, if specified, ignore any rows in the input range that are manually hidden. To use this feature specify one of the parameter values listed below for the first parameter. Note that SUBTOTAL ignores rows hidden using AutoFilter regardless of what the parameter is.

   101 - AVERAGE
   102 - COUNT
   103 - COUNTA
   104 - MAX
   105 - MIN
   106 - PRODUCT
   107 - STDEV
   108 - STDEVP
   109 - SUM
   110 - VAR
   111 - VARP

Kevin
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35089932
Hello,

the attached list is not filtered. To return the maximum number you can use

=max(D5:D6)

but I guess it's more complex in your real spreadsheet.

cheers, teylyn
0
 

Author Comment

by:Gazza83
ID: 35089997
I only want to return data that falls between the criteria listed in both D5:D6 "Stats table"

So the =SUMPRODUCT((Data!B14:B20000+0>=D5)*(Data!B14:B20000+0<=D6)* part is trying to match up the dates from D5:D6 with those in coumn B "Data" sheet

Unfortunatly if I use "=MAX(Data!X14:X2000)" this would return data that doesn't always fall between the dates in D5:D6

i.e B19 = 11/03/2010

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35090027
Use this array formula:

=MAX(IF((Data!B14:B20000>=D5)*(Data!B14:B20000<=D6)>0,Data!B14:B20000,""))

Enter by pressing CTRL+SHIFT+ENTER.

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35090039
Correction:

=MAX(IF((Data!B14:B20000>=D5)*(Data!B14:B20000<=D6)>0,Data!X14:X20000,""))

Kevin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now