In the attached example, I would like to take the average of the numer of units in row 5 based on the criteria in row 7.
So I want to take the last dat in column CF and take the average last 8 weeks of units based but only if the is a "RP" in row 7.
Not sure how to go about doing this with an averageif statement.
Thanks!
Not sure where you want this to be, but try this...
=AVERAGEIF(OFFSET($A$1,6,MATCH(MAX(4:4),4:4,0)-(7*8),,56),"RP",OFFSET($A$1,4,MATCH(MAX(4:4),4:4,0)-(7*8),,56))
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
Do you want to look at only the last 8 columns and extract the average of the RP value from those.....or do you want to average the last 8 that contain "RP"?
If it's the former try this formula
=AVERAGEIFS(5:5,7:7,"RP",4:4,">="&LARGE(4:4,8))
If it's the latter try this "array formula"
=AVERAGEIFS(5:5,7:7,"RP",4:4,">="&LARGE(IF(7:7="RP",4:4),8))
confirmed with CTRL+SHIFT+ENTER
Audere est facere
DDL, the OP wanted 8 weeks, and after your post, I noticed I thought each column was a day, not a week...thanks need to revise my formula...
=AVERAGEIF(OFFSET($A$1,6,MATCH(MAX(4:4),4:4,0)-8,,56),"RP",OFFSET($A$1,4,MATCH(MAX(4:4),4:4,0)-8,,56))
Last edited by FDibbins; 12-23-2013 at 09:08 PM.
Thank you but I am still having an issue.
If I want the avergage of the last 8 weeks(Row 5) if row 7 contains "RP" and I want this value returned in cell CF8 which formula do I use?
I want to be able to copy this formula across row 8 so that when I dump new data in new rows it will automatically populate a new average number.
For the value I need in CF8, to calculate it manually is would need the average of: BS8, BT8, BY8, BZ8, CA8, CC8, CD8 and CE8 which would give me the value of 4129.75
Thank you so much for the much needed help!
You can put that formula wherever you want, and you can copy is across, it will self-adjust
I still can't seem to get it to work.
When I copy the formula in row 8 the value stays the same...and I'm not getting the 4129.75 value that I should be getting in CF8.
Thanks!
The second formula I posted will give you exactly that value if entered with CTRL+SHIFT+ENTER
.....but if you want a formula to copy across from CF8 use this formula in that cell
=IF(CF4="","",AVERAGEIFS($A5:CF5,$A7:CF7,"RP",$A4:CF4,">="&LARGE(IF($A7:CF7="RP",$A4:CF4),8)))
again confirmed with CTRL+SHIFT+ENTER and copied across
If the row 4 value is empty it won't display anything - once you populate that cell it will show a result
To confirm with CTRL+SHIFT+ENTER put formula in cell, press F2 to select formula the hold down CTRL+SHIFT keys and press ENTER. If done correctly you get curly braces like { and } around the formula in the formula bar. Do that with a single cell then copy across
See attached, I copied the formula back down the row as far as where I got errors - does that look as expected?
Last edited by daddylonglegs; 12-23-2013 at 09:23 PM.
Daddylonglegs -
If I needed to add in another criteria say in row 12, how would I go about adding this in?
Thanks!
Perfect!
Thanks daddylonglegs!!!
Daddylongleggs - Here is the example.
Thanks for your help!
If you want the average of the last 8 rows where both criteria are satisfied try this version in CF8
=IF(CF4="","",AVERAGEIFS($A5:CF5,$A7:CF7,"RP",$A12:CF12,"a",$A4:CF4,">="&LARGE(IF($A7:CF7="RP",IF($A12:CF12="a",$A4:CF4)),8)))
Thanks Daddylonglegs!
The formula works, however I don't quite understand how everything works together.
If you could explain how this formula works it would be much appreciated!
Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks