Max and Min formula

[wx4usa]

I have sales data on a worksheet. The data entries contain the day in
column A, month in column B, the year in column C and corresponding
sales figures in column D.

THere can be many lines of data for January 2006, not just one per day.

What formula do I use to return the maximum(Max) sales figure for the
month if cell a1 contains the year (2006) and a2 contains the month
(January) ?

Thanks for your help in this!

 

[Don Guillett]

This is an array formula so enter using ctrl+shift+enter
if valid dates in col A
=MAX(IF((YEAR(daterng)=W1)*(MONTH(daterng)=W2),sumrng))
for your layout. change jan to g1 or wherever
=MAX(IF((C2:C22=F1)*(B2:B22="jan"),D222))

 

[wx4usa]

Don,

Great it worked fantastic.
Waht is this array entry CSE? What does it do and why? Works when I
do it and does'nt when I dont? When should I use CSE???

Thanks you very much! Happy new year.

 

[orbii]

This is an array formula so enter using ctrl+shift+enter

hey don, where can i get more info on how this ctrl+shift+enter? or how
this array formula works? i'm going mad! aloha, orbii

 

[Dave Peterson]

Chip Pearson explains array formulas:
http://cpearson.com/excel/array.htm

 

[orbii]

This is an array formula so enter using ctrl+shift+enter
if valid dates in col A
=MAX(IF((YEAR(daterng)=W1)*(MONTH(daterng)=W2),sumrng))
for your layout. change jan to g1 or wherever
=MAX(IF((C2:C22=F1)*(B2:B22="jan"),D222))

i just ran this agaist somewhat over 30,000 records in excel 2007. the
calculation time is slow as a turtle... does it run slow in the older
versions?

aloha, orbii

 

[Peo Sjoblom]

An array formula is slow on a large dataset, you can use multiple formula
instead
or the DMAX, for ease of use I would probably use a help column and a
formula like

=IF(AND(YEAR(B2)=W1,MONTH(B2)=W2),C2,"")

where B is the date range and C the amount range

then copy that formula 30000 rows in a help column, then
use

=MAX(D230000)

You could also use a filter, custom filter for year and month, then use the
subtotal function

=SUBTOTAL(4,D23000)



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[KC Rippstein]

I am curious to know why you feel the need to separate your month/day/year
into separate columns on your data entry screen. If you would just put the
full date in column A, you could just use a pivot table or a conditional
filter with subtotals to accomplish this task much more simply.

Excel gets extremely slow working with array formulae on large datasets, so
they are generally only practical in limited situations. I've had it slow
to a crawl on just 500 records x 12 columns (which, IMO, is not a large
dataset but made Excel choke for a good minute...once I removed the array
idea and fixed my spreadsheet design for a better logical process, the
delays went away instantly).

- KC Rippstein

 

[wx4usa]

Hi KC,

I may be (probably am) doing it wrong, but I am putting mm/dd/yyyy in
column a and column b converts to day, c to month and d to year and e
to the weekday. This allows me flexibility in the reports I run. I can
pull up by year, month, date, or weekdays.

Is there a better way to do this? These other date columns I hide too.
I am using -text(a1,"ddd") and =year(a1) to convert these dates

 

[KC Rippstein]

Oh, goodness, I'm not suggesting anything's wrong. I was just curious.

The general idea is usually to have your data entry done separately from
your reporting. It is often helpful to break things down into simple
components as you have done to gain greater flexibility on manipulating your
drill-down levels. It just sounded to me like the type of drill-downs you
are filtering for could be automated with a pivot table, and you could
pretty easily add max and min data to a pivot report. Or, doing it the way
you have it now, you can just filter your list for year then month and have
a totals key that uses the SUBTOTAL function for max, min, count, sum,
whatever you want.

It sounds like your current setup works for you. My only suggestion would
be to make sure you have the data set up as a list if you haven't already
done so. As a general rule I keep data entry to the left columns and
formula columns to the right of my data entry. Then I don't have to tab
over any columns when I enter data...just type, Tab, type, Tab, type, and
Enter if my data is in columns A:C and formulas are in D:whatever.

 

[wx4usa]

KC,

Thanks for the input. I appreciate your help and ideas. You have been
very gracious in lending your knowledge.

Rather than a pivot table, Im using sumproduct because of multiple
users. I was afraid they might mess up my tables.

Thanks a bunch!

Happy New Year!

 

[orbii]

ok, i've used formulas and adding of columns to solve some of my problems.
but what's kicking me in my ass is i have no clue how else to do the max()
thing w/o having to resort to using VBA to do the job. could you guys
please take a look at my excel and tell me what else i'm doing wrong? or
could be done better .... w/o using vba or array formulas

highlighted yellow, excel 2007... not sure if some of the formula works in
03

http://www.orbii.com/Book1.xlsx

col 1 - allows me to vlookup the unique dates
col 2 - allows me to vlookup the unique invoice #

w/ that two column, i can now use formula to do anything i want w/o having
to result to using the filter for unique thing.

aloha, orbii

 

[KC Rippstein]

Sorry, I have no idea how to open your file. My PC wants to open your xml
files with Macromedia Dreamweaver, and I see no Excel spreadsheet.

 

[orbii]

i'm using excel 2007, not sure if the formula i'm using works in 2003 that's
why i didn't save for 2003. thanks.