SOLVED
Home

Using a row value criteria in an averageifs function

%3CLINGO-SUB%20id%3D%22lingo-sub-782539%22%20slang%3D%22en-US%22%3EUsing%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782539%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20trying%20to%20create%20a%20formula%20that%20takes%20in%20a%20range%20of%20cells%20and%20averages%20the%20cells%20in%20that%20range%20that%20have%20a%20value%20%26gt%3B%3D0%25%20and%20row%20number%20%26gt%3B%3D%20a%20number%20stored%20in%20another%20cell.%20Is%20this%20possible%3F%20I%20am%20trying%20to%20make%20use%20of%20averageif(s)%20function%2C%20but%20can%20not%20figure%20out%20how%20to%20make%20use%20of%20the%20row()%20function%20nested%20within.%20Really%20appreciate%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-782539%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-782825%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385286%22%20target%3D%22_blank%22%3E%40nickaho%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20recommendation%20for%20you%20to%20get%20an%20answer%20is%20to%20provide%20sample%20data%20in%20an%20Excel%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783191%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385286%22%20target%3D%22_blank%22%3E%40nickaho%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20data%20is%20in%20column%20A%20and%20row%20number%20to%20start%20is%20in%20C1%2C%20when%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DAVERAGEIF(INDEX(A%3AA%2C%24C%241)%3AA1000%2C%22%26gt%3B%3D0%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eassuming%20you%20are%20within%201000%20rows%20range%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783343%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385286%22%20target%3D%22_blank%22%3E%40nickaho%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20best%20if%20you%20store%20your%20variables%20in%20Cells.%20For%20example%2C%20if%20the%20values%20are%20in%20Column%20A%2C%20the%20Start%20Row%20is%20stored%20in%20C2%2C%20End%20Row%20in%20C3%2C%20and%20Percent%20in%20C4%2C%20then%20the%20formula%20to%20return%20your%20desired%20result%20would%20be%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DAVERAGEIF(INDEX(A%3AA%2CC2)%3AINDEX(A%3AA%2CC3)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22%26gt%3B%3D%22%26amp%3BC4)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788453%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you!%20This%20is%20perfect%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788455%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BAwesome%2C%20thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788618%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20row%20value%20criteria%20in%20an%20averageifs%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788618%22%20slang%3D%22en-US%22%3EYour%20gratitude%20delights%20me!%3C%2FLINGO-BODY%3E
nickaho
New Contributor

Hi, I am trying to create a formula that takes in a range of cells and averages the cells in that range that have a value >=0% and row number >= a number stored in another cell. Is this possible? I am trying to make use of averageif(s) function, but can not figure out how to make use of the row() function nested within. Really appreciate the help!

6 Replies

@nickaho 

My recommendation for you to get an answer is to provide sample data in an Excel file.

@nickaho 

 

If your data is in column A and row number to start is in C1, when like

=AVERAGEIF(INDEX(A:A,$C$1):A1000,">=0")

assuming you are within 1000 rows range

 

Highlighted
Solution

@nickaho 

It would be best if you store your variables in Cells. For example, if the values are in Column A, the Start Row is stored in C2, End Row in C3, and Percent in C4, then the formula to return your desired result would be: 

=AVERAGEIF(INDEX(A:A,C2):INDEX(A:A,C3),

">="&C4)

@Sergei Baklan Thank you! This is perfect

@Twifoo Awesome, thank you so much!

Your gratitude delights me!
Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Which formula to use
Ramon Haagen in Excel on
2 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies
if then formula help
BMARSH99 in Excel on
1 Replies