help!

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I need to know if this can be done. On sheet 2 I have 3 columns (A,B,C);
date from, date to and interest rate. I need a formula that would allow me
on sheet 1 to input a date in one cell (D8) and have the corresponding
interest rate (as shown on sheet 2) appear in another (F8). I've been
messin with this for weeks and am at my wits end :)

Thanks, Jim
 
As long as the dates don't overlap you could use

=SUMPRODUCT(--(Sheet2!A2:A200>=D8),--(Sheet2!B2:B200<=D8),C2:C200)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bad logic. Try


=SUMPRODUCT(--(Sheet2!A2:A200<=D8),--(Sheet2!B2:B200>=D8),Sheet2!C2:C200)
 
Neither works, getting #REF errors, I tried changing it slighly to match the
cells I actually use in the columns to no avail.. Just to make sure as I
have limited experience with excel, I am entering this formula into F8 cell
on sheet1 (Where I want the results to show)

=SUMPRODUCT(--(Sheet2!A3:A36>=D8),--(Sheet2!B3:B36<=D8),C3:C36)


Jim
 
The formula I gave you works fine in a test workbook. Again, the
comparisons you're making are inverted logic - the comparisons should be

<start date range> <=D8

and

<end date range> >=D8

The #REF! error would indicate that your references were bad - are you
sure that your sheet is named "Sheet2"?
 
That was embarrassing, lol.
I'm getting a response altho it's not returning the same interest rate as on
"Sheet2". On the first date range it's showing 89.75% when it should be 9%.
Somethings getting lost in the translation, however I'm closer than I've
been todate and thats a big step forward.

Thanks for your help, Jim
 
Are you sure that there is only one rate that matches the date in D8. If
there are many, this formula will sum them.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top