Two worksheets holding wkly & daily Stock Market & Other Stats

  • Thread starter Thread starter duane
  • Start date Start date
here is a non-array formula which calcs the low low of a week

=MIN(OFFSET(E$6,MATCH($G5,$A$7:$A$12,1),0,1,1):OFFSET(E$6,MATCH($G$6,$A$7:$A$12,1)-1,0,1,1))

the daily dates are in col a, the daily lows are in col e, and dates i
col g mondays

max works just the sam
 
Im trying to self educate myself to develop linked worksheets with
Monthly/Year to Date & High/Low values for my above worksheets.
I can do basic formulas, yet have all this info and I can't get past daily
& weekly comparisons.
Right now, Im looking at Array formulas & Functions like MAXA (suppose there
is a MINA style function as well) to get peak values for a time line etc.
but I need some prompting and examples to get my juices working.
Im an old timer about to go to Excel 2004 (from 98) but happy to have a go
from any informed source.
Thanks Graeme
 
Here is an array formula I just did for a client where:
arcolf is a dynamic range of dates
arcols is the net gain/loss on a stock trade
b3 is a starting date and b4 is an ending date

=MAX(IF(ARColF>=$B$3,ARColF<=$B$4),ARColS)

Since this is an array formula, it must be entered/edited with
ctrl+shift+enter vs just enter.
====
here is one that is NOT array entered that gives the total net profit
=SUMPRODUCT((ARColFt>=$B$3)*(ARColFt<=$B$4)*ARColSt)
 
Back
Top