IF statements.. long

  • Thread starter Thread starter nca77
  • Start date Start date
N

nca77

hello,

Column A has interest rates
Column B has volumes
Column C has dates

Row 1 (starting in column E) has month end dates from April 30, 2004 -
April 30, 2010

each month i need to paste new data into columns A - C and i need a
formula to put into row 2 below the month end dates that will try to
match the months and years in column C to the months and years in Row
1. If they match, I need the formula to take the interest rates in row
A times the volumes in Row B and divide that results by the sum of the
volumes in row C that match the months and years in row 1.

I'm sure this is a "sumproduct" formula but im having trouble with the
"IF" statement part of it.

Thanks in advance

Nick
 
Nick,

I'll leave you to test this.

=SUMPRODUCT((MONTH($C$2:$C$23)=MONTH(E1))*(YEAR($C$2:$C$23)=YEAR(E1))*$A$2:$
A$23*$B$2:$B$23)/SUMPRODUCT((MONTH($C$2:$C$23)=MONTH(E1))*(YEAR($C$2:$C$23)=
YEAR(E1)))
 
thanks for the response,

i think im very close to the solution, i will try to be very clea
explaining where i am at.

i have interest rates from A4:A301
i also have volume in cells B4:B301
and i also have various dates from C4:C301
then D4:D301 is a "=year(C4)"
then E4:E301 is a "=month(C4)"

i also have month end dates from F1:AY1
then F2:AY2 is a "=year(F2)"
and F3:AY3 is a "=month(F2)"

this is the formula that i am using in cells F4:AY301

=IF((AND($D$4:$D$301=F$3,$E$4:$E$301=F$2)),SUMPRODUCT($A4,$B4),"0")

then i copy all the data in column A, B & C down to row 303 and do th
same "=month" and "=year" formulas and then use this formula for th
rest

=IF((AND($D$303:$D$600=F$3,$E$303:$E$600=F$2)),$B303,"0")

then at the bottom i sum each column starting in row 303 to row 600 an
divide the formula above by the sum of the formulas below..

here is an example of the data i have and the result i need..

Column A Column B Column C
1.02 73 4/20/04
1.12 80 4/29/04

first i need the sum of column B.. "153" but only if it matches th
month and year of my columns above

then i need a sumproduct(columnA,ColumnB)/153 = 1.073

thanks.. i know there has go to be an easier way to do this.. my fil
is up to 15,114 KB!

thanks again

Nic
 
Just started to read your post and you've got a lot of conflicting
information right at the beginning!

<<" i also have month end dates from F1:AY1
then F2:AY2 is a "=year(F2)"
and F3:AY3 is a "=month(F2)">>
Click to expand...
The above doesn't make sense to me!
Circular reference in F2 ???
What's in F3?
Should they both refer to F1??


And, without even reading your entire formula, your formula starts out:
<<"=IF((AND($D$4:$D$301=F$3,">>
Where column D is supposed to contain "=YEAR(C4)",
And F3 is supposed to contain "=month(F2)", which is probably incorrect
also!

Too many inconsistencies to offer any viable suggestions.
 
ok what i am trying to do is this..

to make it simple i will use letters to represent the data.

in "data sheet"

each month i'll be importing data into columns A - C

C will always have a dates

in "calc sheet" im going to have dates in the top row. they will b
"hard keyed" dates starting 1/31/04 going out 60 columns to 12/31/08.

formula 1
needs to sum column B if the month and year match the month and year i
my calc sheet.. to give me monthly totals

formula 2
i need another formula to scan again to match both months and years.
then multiply A*B if they match, then add those values after th
multiplication.

then i can have a simple formula 2 / formula 1 = my desired result

hope that makes sense, thank
 
Here is your Formula #1.
It totals the rows in column B, where the dates in column C match the year
and month in E1.

=SUMPRODUCT((DATE(YEAR($C$4:$C$301),MONTH($C$4:$C$301),DAY(E1))=DATE(YEAR(E1
),MONTH(E1),DAY(E1)))*$B$4:$B$301)


Here is your Formula #2.
It multiplies the rows in column A by the rows in column B,where the dates
in column C match the year and month in E1, and then sums the products of
the multiplication.

=SUMPRODUCT((DATE(YEAR($C$4:$C$301),MONTH($C$4:$C$301),DAY(E1))=DATE(YEAR(E1
),MONTH(E1),DAY(E1)))*$B$4:$B$301*$A$4:$A$301)


Now, here is a combined formula, that does all of the above, *plus* divides
Formula #2 by Formula #1:

=SUMPRODUCT((DATE(YEAR($C$4:$C$301),MONTH($C$4:$C$301),DAY(E1))=DATE(YEAR(E1
),MONTH(E1),DAY(E1)))*$B$4:$B$301*$A$4:$A$301)/SUMPRODUCT((DATE(YEAR($C$4:$C
$301),MONTH($C$4:$C$301),DAY(E1))=DATE(YEAR(E1),MONTH(E1),DAY(E1)))*$B$4:$B$
301)

All of these formulas can be dragged across the columns to be copied.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


ok what i am trying to do is this..

to make it simple i will use letters to represent the data.

in "data sheet"

each month i'll be importing data into columns A - C

C will always have a dates

in "calc sheet" im going to have dates in the top row. they will be
"hard keyed" dates starting 1/31/04 going out 60 columns to 12/31/08.

formula 1
needs to sum column B if the month and year match the month and year in
my calc sheet.. to give me monthly totals

formula 2
i need another formula to scan again to match both months and years..
then multiply A*B if they match, then add those values after the
multiplication.

then i can have a simple formula 2 / formula 1 = my desired result

hope that makes sense, thanks
 
First let me thank you for your patience!

then let me thank you an awesome formula!

it does exactly what i needed it to do and now my file is 220kb vs th
29MB!

your help is VERRRRRRRY Appreciated!

thanks:) :) :
 
Thanks for the feed-back.
I wouldn't want to go through that exercise again!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

First let me thank you for your patience!

then let me thank you an awesome formula!

it does exactly what i needed it to do and now my file is 220kb vs the
29MB!

your help is VERRRRRRRY Appreciated!

thanks:) :) :)
 
Back
Top