Your best formula creations

[alphadown]

in Kindergarden Cop style:
What are your most complex/useful/creative formulas and what do they do?

These probably aren't that advanced to a lot of you, but I feel they were big steps for me when I made them.

=IF(OFFSET(A1,2,$D$20-1)="",#N/A,OFFSET(A1,2,$D$20-1))

Copied down, it returns values from a particular row of variables (depending on the number in D20), including the column label, for use in a scatter plot with a trendline. There is a similar formula for the adjacent row for the other axis. Blanks are changed to #N/A so they don't return 0 and stuff up the scatter/trendline. Handy if you need a relatively quick visual check on relationships between large numbers of variables.


=AVERAGE(INDIRECT(ADDRESS(((ROW()-1)*6)-4,10,,,"Raw")):INDIRECT(ADDRESS(((ROW()-1)*6+1),10,,,"Raw")))

Copied down, it returns the average of every 6 cells in Column J in sheet "Raw", with compensation for a header row.

 

[Puuuuma]

I'm so glad I found this thread. I am so proud of this formula, and haven't had nearly enough people appreciate it. I created this formula to search through a list of sales contracts and place them on a calendar so we can better plan our shipping schedule. It looks for the date and shipment number (to account for multiple shipments on the same day), and then places it in the calendar, or returns a blank cell if it has already shipped, or there are no orders for that day.

{=IF(ISNA(INDEX(SaleNumber,MATCH(E53&$A58,ShipDate&ShipmentNumber,0)))," ",CONCATENATE(INDEX(SaleNumber,MATCH(E53&$A58,ShipDate&ShipmentNumber,0)),": ",INDEX(CoName,MATCH(E53&$A58,ShipDate&ShipmentNumber,0))," ",INDEX(Material,MATCH(E53&$A58,ShipDate&ShipmentNumber,0))))}

This was the first time I had used index, match, ISNA, and named ranges

 

[Greg Truby]

See the corresponding thread in the lounge from about 2½ years ago.

http://www.mrexcel.com/forum/showthread.php?t=148186