I need to get excel to follow this pattern. (skips DE, GH and retains row 4:6)
=AVERAGE(sheet!C$4:C$6)
=AVERAGE(sheet!F$4:F$6)
=AVERAGE(sheet!I$4:I$6)
When i drag down it gives me this.
=AVERAGE(sheet!C$4:C$6)
=AVERAGE(sheet!C$4:C$6)
=AVERAGE(sheet!C$4:C$6)
please see attached.
Thank you
Last edited by my1980; 10-13-2015 at 08:39 PM.
Welcome to the board.
If you deleted the empty columns on Sheet2, you could copy the formulas in the usual way.
Entia non sunt multiplicanda sine necessitate
Another way would be to use name ranges. Create a range for each column and give it the same name as the heading Girl, Boy etc.
Then use this formula...
=AVERAGEIF(Sheet2!$B$4:$B$8,Summary!F$3,INDIRECT(E5))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
"Create a range for each column and give it the same name as the heading Girl, Boy etc."
Please i do not understand what you mean by this.
Can you please explain further?
Or attach in excel?
Thanks.
Alternatively, copy this into F5 and drag down. copy from F5 to H5 and drag down.
=IFERROR(AVERAGEIF(Sheet2!$B$4:$B$8,Summary!F$3,INDIRECT("Sheet2!"&LEFT(ADDRESS(1,ROWS($1:1)*3,4),LEN(ADDRESS(1,ROWS($1:1)*3,4))-1)&"4:"&LEFT(ADDRESS(1,ROWS($1:1)*3,4),LEN(ADDRESS(1,ROWS($1:1)*3,4))-1)&"8")),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
You can use this sheet which has the solution. Hope, this works for you.
Hello Glenn,
Sorry still did not get it. I have transferred the formular to my main sheet.
Please see attached.
Many thanks for your help thus far.
Hi All,
a different approach,
in F3: Non Supervisory (instead of "Non - Supervisory")
in E14: Emotional Control and Composure (instead of "Emotional Control")
In order to easily match results
In F5 to be copied across:
=IFERROR(SUMPRODUCT(Sheet!$C$3:$AK$8,(Sheet!$B$3:$B$8=F$3)*(Sheet!$C$3:$AK$3=$E5))/SUMPRODUCT((Sheet!$B$3:$B$8=F$3)*(Sheet!$C$3:$AK$3=$E5)),0)
Hope it helps
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
There were three mistakes in your sheet. The formula should have been this:
Formula:
Please Login or Register to view this content.
They were: in column H the wrong range in column B had been selected; in F3, there was a hyphen between non and supervisory and in column H the sheet name was incorrect.
hi glen. Many thanks for your help so far. But i am not an advanced user, so if you dont mind helping me translate the formular so that i can apply it to a different sheet which has more reference columns.
I know you are used to dealing with professionals, but i am still on my way there.
IFERROR(AVERAGEIF(Sheet!$B$4:$B$8,Summary!F$3,INDIRECT("Sheet!"&LEFT(ADDRESS(1,ROWS($1:3)*3,4),LEN(ADDRESS(1,ROWS($1:3)*3,4))-1)&"4:"&LEFT(ADDRESS(1,ROWS($1:3)*3,4),LEN(ADDRESS(1,ROWS($1:3)*3,4))-1)&"8")),"")
how do i adjust this? what does this translate to? thanks
To be honest, I didn't think of using SUMPRODUCT, as canapone did. I would strongly recommend that you use his formula. It's far easier to manage than mine. Hopefully you can see how to add on additional criteria.
To create a named range, highlight the range (say, the list of Girls), right click on it and select "Define Name" call it Girls.Originally Posted by my1980
Repeat this for all the others, but make sure the name you give it, matches exactly, the name you have in your table. If the name in the table is Girls (with an s), call the range Girls. If the name in the table is Girl (without an s) then call the range Girl
I have to say this is the best forum on the internet. Thanks all for all your help. Canapone's formular worked best.
Special thanks to Glen as well.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks