SOLVED
Home

Formula with % and averages???

%3CLINGO-SUB%20id%3D%22lingo-sub-643619%22%20slang%3D%22en-US%22%3EFormula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643619%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%3C%2FP%3E%3CP%3EI'm%20hoping%20for%20a%20solution%20to%20a%20wee%20problem...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20the%20average%20of%20a%20number%20of%20cells.%20%26nbsp%3BThere%20is%20a%20formula%20in%20each%20of%20these%20cells%2C%20some%20cells%20are%20populated%20with%20data%20and%20some%20are%20not.%20%26nbsp%3BThe%20ones%20that%20are%20not%2C%20have%20the%20value%20of%200%25%20displayed%20and%20will%20have%20until%20information%20is%20entered.%20Data%20won't%20be%20entered%20in%20for%20some%20time.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I'm%20having%20is%20that%20the%20the%20average%20%25%20being%20returned%20is%20including%20the%200%25's%20and%20thereby%20reducing%20the%20average.%20%26nbsp%3BIs%20there%20any%20way%20around%20this%2C%20do%20you%20think%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20story%20is%20related%20to%20percentage%20attendance%20at%20events.%20%26nbsp%3B7%20in%20total%20throughout%20the%20year%2C%20so%20I'm%20looking%20to%20return%20the%20average%20attendance%20for%20the%20periods%20that%20have%20passed%20only%2C%20but%20I%20do%20what%20to%20have%20the%20formulas%20in%20the%20other%20cells%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643619%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644137%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141213%22%20target%3D%22_blank%22%3E%40Alison%20Flynn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%2C%26nbsp%3B%3CSPAN%3E%3DAVERAGEIF(a1%3Aa100%2C%22%26lt%3B%26gt%3B0%22)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644157%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644157%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rusty%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20this.%20%26nbsp%3BNot%20sure%20how%20it%20will%20fit%20in%20to%20the%20formula%20I%20have%20-%20here's%20what%20I've%20got%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAVERAGE(FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%20%26nbsp%3B-%20yes%2C%20a%20big%20database!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20%3DAVERAGE(FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6%2C%22%26lt%3B%26gt%3B0%22)%20but%20it's%20returning%20a%20%23value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644240%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141213%22%20target%3D%22_blank%22%3E%40Alison%20Flynn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20'AverageIF'%2C%20not%20just%20'Average'%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644334%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644334%22%20slang%3D%22en-US%22%3E%3CP%3EAfraid%20that's%20not%20working%20-%20'too%20much%20arguments%20for%20the%20function'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F50479%22%20target%3D%22_blank%22%3E%40Rusty%20Dane%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644468%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141213%22%20target%3D%22_blank%22%3E%40Alison%20Flynn%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUM(FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%2FINDEX(FREQUENCY((FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%2C0)%2C2)%3C%2FPRE%3E%0A%3CP%3ESource%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.exceldashboardtemplates.com%2Fhow-to-averageif-excluding-zeros-and-blanks-for-non-contiguous-ranges-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.exceldashboardtemplates.com%2Fhow-to-averageif-excluding-zeros-and-blanks-for-non-contiguous-ranges-in-excel%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644507%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644507%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DSUM(FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%2F%3CBR%20%2F%3E((FE6%26lt%3B%26gt%3B0)%2B(HJ6%26lt%3B%26gt%3B0)%2B(JO6%26lt%3B%26gt%3B0)%2B(LT6%26lt%3B%26gt%3B0)%2B(NY6%26lt%3B%26gt%3B0)%2B(QD6%26lt%3B%26gt%3B0)%2B(SI6%26lt%3B%26gt%3B0))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644529%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644529%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20million%20for%20sending%20this%20formula%20on%20and%20for%20taking%20the%20time%20to%20do%20so.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20afraid%20it's%20not%20actually%20returning%20the%20correct%20answer.%20%26nbsp%3BI've%20been%20given%20a%20different%20formula%20that%20seems%20to%20be%20doing%20so%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUM(FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%2FINDEX(FREQUENCY((FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%2C0)%2C2)%3C%2FPRE%3E%3CP%3EReally%20appreciate%20your%20time.%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%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%3Bwrote%3A%3CBR%20%2F%3ETry%20this%3A%3CBR%20%2F%3E%3DSUM(FE6%2CHJ6%2CJO6%2CLT6%2CNY6%2CQD6%2CSI6)%2F%3CBR%20%2F%3E((FE6%26lt%3B%26gt%3B0)%2B(HJ6%26lt%3B%26gt%3B0)%2B(JO6%26lt%3B%26gt%3B0)%2B(LT6%26lt%3B%26gt%3B0)%2B(NY6%26lt%3B%26gt%3B0)%2B(QD6%26lt%3B%26gt%3B0)%2B(SI6%26lt%3B%26gt%3B0))%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%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%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644533%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644533%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20done%20it%20again!%20%26nbsp%3BThat%20works%20perfectly.%20%26nbsp%3BThank%20you%20so%20much%2C%20much%20appreciated.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%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%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644594%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20with%20%25%20and%20averages%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644594%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alison%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%20Formula%20which%26nbsp%3B%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%3B%20suggested%20also%20works%20if%20only%20you%20have%20blank%20or%20zero%20cells.%20If%20you%20have%20empty%20strings%20in%20your%20cells%2C%20e.g.%20returned%20by%20something%20like%20%3DIF(%3CA%3E%2C%22%22%2Cnumber)%2C%20you%20may%20use%20it%20changing%200%20on%20%22%22.%20If%20you%20need%20to%20check%20both%20the%20variant%20could%20be%20...%2F((LEN(FE6)%26gt%3B0)%2B...%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141213%22%20target%3D%22_blank%22%3E%40Alison%20Flynn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Alison Flynn
Contributor

Hey guys,

I'm hoping for a solution to a wee problem...

 

I'm looking for the average of a number of cells.  There is a formula in each of these cells, some cells are populated with data and some are not.  The ones that are not, have the value of 0% displayed and will have until information is entered. Data won't be entered in for some time.  

 

The problem I'm having is that the the average % being returned is including the 0%'s and thereby reducing the average.  Is there any way around this, do you think?

 

The story is related to percentage attendance at events.  7 in total throughout the year, so I'm looking to return the average attendance for the periods that have passed only, but I do what to have the formulas in the other cells in advance.

 

Your help would be greatly appreciated!

Cheers

9 Replies

@Alison Flynn 

 

Try this formula, =AVERAGEIF(a1:a100,"<>0")

Hi Rusty,

 

Thanks for this.  Not sure how it will fit in to the formula I have - here's what I've got:

 

=AVERAGE(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)  - yes, a big database!

 

I tried =AVERAGE(FE6,HJ6,JO6,LT6,NY6,QD6,SI6,"<>0") but it's returning a #value.

 

Any thoughts?

@Alison Flynn 

Use 'AverageIF', not just 'Average'

Afraid that's not working - 'too much arguments for the function'

 

???

 

@Rusty Dane 

Solution

@Alison Flynn , that could be

=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/INDEX(FREQUENCY((FE6,HJ6,JO6,LT6,NY6,QD6,SI6),0),2)

Source is here https://www.exceldashboardtemplates.com/how-to-averageif-excluding-zeros-and-blanks-for-non-contiguo...

 

Try this:
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/
((FE6<>0)+(HJ6<>0)+(JO6<>0)+(LT6<>0)+(NY6<>0)+(QD6<>0)+(SI6<>0))

Hi,

 

Thanks a million for sending this formula on and for taking the time to do so.

 

I'm afraid it's not actually returning the correct answer.  I've been given a different formula that seems to be doing so:

 

=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/INDEX(FREQUENCY((FE6,HJ6,JO6,LT6,NY6,QD6,SI6),0),2)

Really appreciate your time.

Cheers


@Twifoo wrote:
Try this:
=SUM(FE6,HJ6,JO6,LT6,NY6,QD6,SI6)/
((FE6<>0)+(HJ6<>0)+(JO6<>0)+(LT6<>0)+(NY6<>0)+(QD6<>0)+(SI6<>0))

@Twifoo 

Hi Sergei,

 

You have done it again!  That works perfectly.  Thank you so much, much appreciated.  

 

@Sergei Baklan 

Hi Alison,

 

You are welcome. Formula which @Twifoo  suggested also works if only you have blank or zero cells. If you have empty strings in your cells, e.g. returned by something like =IF(<A>,"",number), you may use it changing 0 on "". If you need to check both the variant could be .../((LEN(FE6)>0)+...

 

@Alison Flynn 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies