Fulfilling a sum requirement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone i just needed some help on a summing function. On one side i
have two horizontal rows. The first being dated seperated on a week by week
format for about 148 weeks. Under this row is another with a small numerical
value. In another place is a set of 12 larger numbers. I would like to make
a timeline trying to evaluate when each of the larger 12 numbers were met
when summing the smaller 148 numbers in order (not starting over when first
large number is met). Thanks

heres an example

(Smaller 148)
2/3/2006 2/10/2006 2/17/2006 2/24/2006 3/3/2006 3/10/2006 3/17/2006....
0.86 0.86 0.86 0.86 1.00 1.00 1.00

(Larger 12)
Widget 1: 2.58
Widget 2: 3.86...

(WHAT I NEED "date when met")
Widget 1: 2/17/2006
Widget 2: 3/17/2006
 
Dave,

Put a third row of formulas, like this

=SUM($A2:A2)

into cell A3, for example, then copy to the right to match your row of small numbers.

Then use

=IF(ISERROR(MATCH(B10,$3:$3,FALSE)),INDEX($1:$1,MATCH(B10,$3:$3)+1),INDEX($1:$1,MATCH(B10,$3:$3,FALSE)))

where B10 is the first of the cells with the larger numbers.

HTH,
Bernie
MS Excel MVP
 
Back
Top