SOLVED
Home

Average on basis of particular range

%3CLINGO-SUB%20id%3D%22lingo-sub-796434%22%20slang%3D%22en-US%22%3EAverage%20on%20basis%20of%20particular%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796434%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eanyone%20can%20help%20me%20in%20getting%20average%20on%20basis%20of%20range%20in%20between%202%20values%2C%20sample%20file%20is%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EMusaddiq%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-796434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796480%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20on%20basis%20of%20particular%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796480%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389603%22%20target%3D%22_blank%22%3E%40musaddiq%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3CSPAN%3EMusaddiq%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20you%20have%20multiple%20criteria%20you%20can%20use%20AVERAGEIFS().%20Below%20is%20your%20desired%20formula%20but%20it%20will%20give%26nbsp%3B%3CSTRONG%3E%23DIV%2F0!%3C%2FSTRONG%3Ebecause%20none%20of%20the%20values%20fall%20between%20the%20range%20defined%20in%20your%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3E%3CSTRONG%3E%3DAVERAGEIFS(A2%3AA13%2CA2%3AA13%2C%22%26lt%3B%22%26amp%3BF4%2CA2%3AA13%2C%22%26gt%3B%22%26amp%3BG4)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyhow%20an%20example%20is%20also%20attached%20for%20your%20understanding.%3C%2FP%3E%3CP%3EHope%20it%20will%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
musaddiq
Occasional Visitor

Hi,

 

anyone can help me in getting average on basis of range in between 2 values, sample file is attached. 

 

Thanks,

Musaddiq

1 Reply
Solution

@musaddiq 

 

Hi Musaddiq

 

When you have multiple criteria you can use AVERAGEIFS(). Below is your desired formula but it will give #DIV/0! because none of the values fall between the range defined in your sheet.

 

=AVERAGEIFS(A2:A13,A2:A13,"<"&F4,A2:A13,">"&G4)

 

Anyhow an example is also attached for your understanding.

Hope it will help.

 

Thanks

Tauqeer