Returns Minimum Value in Specific Data

[Ricco Balboa]

Hello

I'm a little stuck.

I have a report I am producing that returns some information regarding accounts we deal with.

it may be best to explain what I have allready.

Firstly I have The total occurences in a specific month and year using the following formula.

Please Login or Register  to view this content.

This looks at the date field 06-2009 returns the total number of occurences in June 09.

Then I have the average number returned using the following.

Please Login or Register  to view this content.

Again looking at 06-2009.

What I need is the actual Minimum number which is in the list that the average is taken from.

For example the minimum number for 06-2009 is 2 from the list 2,5,7,7,6,5,7,5,9.
Ill do the same for the max which will be 06-2009 is 9

Please can someone help?

Thanks
Rich

[NBVC]

tRY:

=MIN(IF('App - Setup Fee'!$P$2:$P1000=A3,'App - Setup Fee'!$AH$2:$AH$10000))

AND

=MAX(IF('App - Setup Fee'!$P$2:$P1000=A3,'App - Setup Fee'!$AH$2:$AH$10000))

Each confirmed with CTRL+SHIFT+ENTER and not just ENTER

[DonkeyOte]

If you are repeating these calcs for different months/years accounts etc you might want to consider using a Pivot Table to do this as it will be more efficient long term.

You can show Count, Average & Max quite easily - and you can group your source data by Month & Year ... if you provide a sample of your source data (confidential info. removed) we could perhaps demo for you ?

[Ricco Balboa]

Hi guys,

I thought of doing a pivot table but this particular report is part of a month end stats report and I want to be able to add and remove information form different areas.

I may produce a pivot table to obtain my raw data and add it to the report itself but for this particular excercise I would prever to create my own table.

Thanks very much Donkey for your suggestion, if I can't crack it I will resort to a pivot.
I'll let you know how I get on.


As for the formula It doesnt seem to work, the first field works fine but when I copy down the formula to get 07-2009 it won't pick up on the the next date, I feel it should though; a 0 is returned.

Any thoughts?

[Ricco Balboa]

Hold up.

Donkey is right. I have knocked up a quick pivot and Its much better.

Thanks guys for your imput.

Rich