Excel Magic Trick 583: AVERAGEIF & IFERROR functions Mixed Cell References Gross Profit Percentage

See how to use the AVERAGEIF & IFERROR functions with Absolute and Mixed Cell References to create a large table of conditional average calculations (averaging with criteria). Table with averages for units sold, sales and COGS for each Sales Rep. Gross Profit Percentage formula.

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• AVERAGEIF
• IFERROR
• functions
• Absolute
• Mixed
• Cell
• References
• to
• table
• of
• conditional
• average
• calculations
• averaging
• criteria
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• units
• sold
• sales
• and
• COGS
• for
• each
• Sales
• Rep
• Gross
• Profit
• Percentage
• formula

[MrGarwil]

If I type the month in as July-11 the the formula returns a correct result but not if the month is linked to another sheet that is formatted as mmm-yy. In fact the evaluation numbers are totally different . The evaluation for Jul when typed in is 40735 and when formatted as mmm-yy is 40739.6... ??? What to do as I need the months to change dynamically when the start-month on the other sheet is changed

[ExcelIsFun]

#DIV/0 simply means that the denominator evaluates to zero. Formulas do not see formatting and so it is not the formatting. I guess you are averaging by month right? What exactly does the cells with the criteria contain? The decimal represents time. If you are just after the integer, then use a formula like this:

=AVERAGEIF($C:$C,INT(H3),$­E:$­E)

The INT function around the H3 will remove decimal.

You can send me a small sample workbook that simultaes he problem to: excelisfun at gmail

[MrGarwil]

@ExcelIsFun Hi thanks for the reply, I tried adding the "INT" to the formula but it did nt solve the problem. How do I send you a file?

Rgds

[ExcelIsFun]

excelisfun at gmail

[MESSER8989]

What do you suppose are the most important functions for accountants?

[ExcelIsFun]

I do not have any evidence, but amongst the most often used by accountants are:

SUM, VLOOKUP, IF, AVERAGE, SUMIF, COUNTIF, SUMIFS, COUNTIFS, SUMPRODUCT, PMT, PV, FV, NPV, XNPV, DDB…

[MrGarwil]

Ok.. am getting somewhere but now have hit a dead-end!

The dates (Months) I am using in my headers (Row / columns H3-S3) are derived form a pasted limk form another work book. The faulty months are (when I 'Evaluate' the formula are evaluation as " =AVERAGEIF($C:$C,"="&40861.2,$­E:$E). Each of the four months that display a #DIV/0 error have a decimal point in the numerical representation of the month. The months that are returning a correct result do not! Now I am stumped..

[MrGarwil]

I have another formula given me that works but extremely slow and locks up my computer for litterlayy hours.. and is

=SUMPRODUCT((TEXT($C:$C,"mmm-y­y")=TEXT(L3,"mmm-yy"))*$E:$E)/­SUMPRODUCT(--(TEXT($C:$C,"mmm-­yy")=TEXT(L3,"mmm-yy")))

[MrGarwil]

Apologies... Column E has the perecentages, not "H" as in previous post..

in Cells H4 thru S4 i want to average the percentages in column "E" where the dates in column "C" matches that in cells H3-S3.

USing this formula =AVERAGEIF($C:$C,"="&H3,$E:$E) only returns one correct result in every four months in cells H3-S3!! All the other months are returning a #DIV/0 error?? What could possibly be wrong where all cells are formatted correctly and same formula being used ??

[MrGarwil]

hi,

Am using this formula (averageif) without the error correction but receiving a #div/0 error on some of the cells I am averaging.

My spreadsheet looks like this...

Columc "C' has dates formatted as dd-mmm-yy from 01-March -11 to 31-Dec-13. Column "H" has percentages representing occupancy for each given day of the range in column "C". Both columns are formatted the same for each type of cell content.

(Cont'd next post)

[ExcelIsFun]

You are welcome!