Return Low Value Month

Posted by Ian on February 27, 2001 12:37 PM

I have a detail spreadsheet (sheet2) that I need to return summary information to on a overview page (sheet2). The detail contains commission amounts for each month in, lets say ,C2:C13. I've set it up to return the high and low value for the year, but was looking for formula that would return the name of the high month and low month. These are located in A2:A13. As a side, the person I'm preparing this for would kill me if a pivot table showed up in the workbook.
Thanks,
Ian

Posted by David Hawley on February 27, 2001 1:13 PM

What! no Pivot Tables, kill him first. :)

For your High month use:
=INDEX(Sheet2!A2:A13,MATCH(MAX(Sheet2!C2:C13),Sheet2!C2:C13),1)

And for the Low
=INDEX(Sheet2!A2:A13,MATCH(MIN(Sheet2!C2:C13),Sheet2!C2:C13),1)

Dave

OzGrid Business Applications

Posted by Ian on February 27, 2001 3:05 PM

Thanks Dave. I keep having a hard time understanding what exactly goes on in the whole Index/Match tandem thing. It didn't seem like these formulas were returning the correct value. After playing around a bit, I think the following works on the summary sheet (sheet 1)

=INDEX(Sheet2!A2:C13,MATCH(MIN(Sheet2!C2:C13),Sheet2!C2:C13,FALSE),1)

Thanks for the help and the post.

Posted by Mark W. on February 27, 2001 3:09 PM

>the person I'm preparing this for would kill
>me if a pivot table showed up in the workbook.

That person should be flogged! ; )