Hello,
I have a averageifs formula:
= AVERAGEIFS(C4:C5000,B4:B5000,Data!B4)
Is there away I can just average the last 10 rolling data entries of column C that meet the criteria in Column B.
Thanks
Rob
Hello,
I have a averageifs formula:
= AVERAGEIFS(C4:C5000,B4:B5000,Data!B4)
Is there away I can just average the last 10 rolling data entries of column C that meet the criteria in Column B.
Thanks
Rob
Last edited by Wolfgang17; 03-24-2014 at 07:40 AM.
Hi,
Would be better if you could attach an actual workbook so that this can be verified, but perhaps this array formula**:
=AVERAGE(IF(B4:B5000=Data!B4,IF(ROW(B4:B5000)>=LARGE(IF(B4:B5000=Data!B4,ROW(B4:B5000)),10),C4:C5000)))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Sorry for the delay in my reply.
I've uploaded the spreadsheet. I'm trying to average last 10 Gallons run of softener #1 in cell G4. As more data (gallons run For Softener #1) are added to column C you get just the last 10 data entries.
Does this make sense.
Thanks for you help.
try use formula
=AVERAGE(IF(B4:B5000=E13,IF(ROW(B4:B5000)>=LARGE(IF(B4:B5000=E13,ROW(B4:B5000)),5),C4:C5000)))
Plz press Ctrl+Shift+Enter to finish input formula
try replace number 5 in above formula (fill red color) to 10 as there are enough or more than 10 items....
Best regard, -)iger-/iger
If you are pleased with a solution mark your post SOLVED.
That worked nicely.
Thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks