Can anybody help me figure out how to drag down a formula that looks like this for the first three cells (C3:C5)?
Is there a way to let Excel know that I'm adding six to each cell, rather than it automatically adding one?PHP Code:=(SUMPRODUCT(Polls!D2:D5,Polls!B2:B5))/(SUM(Polls!B2:B5))
=(SUMPRODUCT(Polls!D8:D11,Polls!B8:B11))/(SUM(Polls!B8:B11))
=(SUMPRODUCT(Polls!D14:D17,Polls!B14:B17))/(SUM(Polls!B14:B17))
...etc.
Thanks!
Hi,
Does this link help?
http://exceltips.vitalnews.com/Pages...a_Pattern.html
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
It looks like that website is exactly what I'm looking for, but I don't know exactly how to use it.
Can anybody possibly help me use the INDIRECT() function for the three formulas posted above, so I can drag the formula down without having to retype hundreds of numbers?
Try:
Note: Adjust each range to suit your entire database.
Please Login or Register to view this content.
Last edited by NBVC; 06-11-2008 at 12:58 PM.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thank you!
You definitely did it. It's still a little gibberish to me, but I think I'm starting to understand it except for the row reference of "A1". What is that supposed to be in reference to?
And could you possibly help me out with doing the same to a similar formula?
Thanks again!PHP Code:=SUMPRODUCT((Polls!D2:D5)*(100-(MAX(Polls!G2:G5)-Polls!G2:G5))*(Polls!G2:G5>MAX(Polls!G2:G5)-100))/SUMPRODUCT((100-(MAX(Polls!G2:G5)-Polls!G2:G5))*(Polls!G2:G5>MAX(Polls!G2:G5)-100))
Last edited by rylock; 06-11-2008 at 01:51 PM.
ROW(A1) is used as a counter... as you drag down the formula, that turns to ROW(A2), ROW(A3), etc.. this is the basis for how the formula steps up your groups of ranges...
Just change each range to:Originally Posted by rylock
INDEX(Polls!$D$2:$D$100,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$100,4+((ROW(A1)-1)*6)
changing the D2:D100 to match the ranges of concern in your original formula.
Thanks for all the help! Sorry for so many questions.
I tried to combine the two to make a massive formula and I'm getting a "#VALUE" error.
Is there anything that you can see wrong with this?
PHP Code:=AVERAGE((SUMPRODUCT(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))*(100-(TODAY()-(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))))*((INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))>TODAY()-100)))/(SUMPRODUCT((100-(TODAY()-(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))))*((INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))>TODAY()-100)))*(SUMPRODUCT(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)),INDEX(Polls!$B$2:$B$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$B$2:$B$164,4+((ROW(A1)-1)*6))))/(SUM(INDEX(Polls!$B$2:$B$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$B$2:$B$164,4+((ROW(A1)-1)*6))))
It's too confusing....
What would an example of the formula be if you were just using the set ranges?
Yea, it looks crazy.
This is the part of the formula (for the first 4 cells) that is acting up without the index references.
This is what I tried to do with adding the index.PHP Code:SUMPRODUCT((Polls!D2:D5)*(100-(TODAY()-Polls!G2:G5))*(Polls!G2:G5>TODAY()-100)))/(SUMPRODUCT((100-(TODAY()-Polls!G2:G5))*(Polls!G2:G5>TODAY()-100)
Hopefully that's a little less confusing. (Even though it's super confusing to me.)PHP Code:((SUMPRODUCT(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))*(100-(TODAY()-(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))))*((INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))>TODAY()-100)))/(SUMPRODUCT((100-(TODAY()-(INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))))*((INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6)))>TODAY()-100))
I just tried to change every "Polls!D2:D5" to "INDEX(Polls!$D$2:$D$164,1+((ROW(A1)-1)*6)):INDEX(Polls!$D$2:$D$164,4+((ROW(A1)-1)*6))"
Try:
Please Login or Register to view this content.
That's works great!
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks