Average even-numbered rows, ignoring #REF! errors

**chrisrye13** · 07-20-2015, 02:12 PM

EDIT: even or odd numbered rows, it doesn't matter as I need to do both!

Hey guys,

I'd describe myself as slightly more advanced than beginner when it comes to Excel, but not much...so please bear with me!

I have just begun managing a bar and would like to analyse our sales a little better. Every night we complete a shift report on a spreadsheet with lots of info including the daily sales. We create a different workbook every day, and I have collated all of that information in one analysis workbook using external references to the daily spreadsheets. The analysis workbook runs on a 13 week basis so that we have 4 throughout the year. Obviously I'd like to analyse this data to some detail, including averages, but this is were the problem arises.

I have completed the external references for the whole 13 week period across the spreadsheet to save having to copy, paste and edit the external references every day to find the right information, but as there is no source workbook for a day that we have not completed a shift report for yet, these external references return a #REF! error. I have created a conditional format to colour the font of any #REF! error white, purely for visual reasons so this in itself isn't a problem, but I am struggling to find a formula to average the data that I'd like because some of the cells that the formula references, return #REF! errors.

I'm hoping there is a way of ignoring #REF! errors in a formula to find averages - the only other spanner in the works is that the data I am trying to average falls on every other row in a column. So for instance Sunday's sales are in columns D and E (morning shift and evening shift respectively). Week 1 is in rows 5 and 6 (5 being for morning and evening sales, and row 6 being for the total).

The data runs from cells D5:S30 including two columns at the end for the weekly total. So I would want a formula to find the average across a 13 week period for each AM, each PM, and each daily total.

If I enter =AVERAGE(D5,D7,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29) the result is #REF! as the shifts in the future have not been completed.

I have found formulas that work for a contigeous range but this is obviously not the case.

I have considered creating shift reports for all 13 weeks in advance, so that the external reference would find its source, and the formula would work, but if there's a formula out there that would save me the hassle, that'd be superb!

Any ideas and suggestions would be amazing!

Chris

**oeldere** · 07-20-2015, 02:15 PM

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

**nigelbloomy** · 07-20-2015, 02:17 PM

Does this do what you need?

Please Login or Register  to view this content.

The 1 means it will find the average. The 6 means it will ignore error values.

**chrisrye13** · 07-20-2015, 02:30 PM

Originally Posted by nigelbloomy

Does this do what you need?

Please Login or Register  to view this content.

The 1 means it will find the average. The 6 means it will ignore error values.

Yes! Thanks so much - that was simpler than I thought. I've been battling at this for weeks!

Thank you!

Chris

**nigelbloomy** · 07-20-2015, 02:33 PM

You are welcome. Glad it was an easy fix.

**chrisrye13** · 07-20-2015, 02:53 PM

nigelbloomy,

I have one more question actually, which I hadn't thought about.

There is a weekly total in columns R and S which is a SUMIF formula to exclude any #REF! results, so it can give me a running total for the week I am in - if I have entered results for Sunday and Monday, but not Tuesday onwards for example. =SUMIF(D10:Q10,"<>#REF!")

This works fine, but now the formula for the average is obviously including that, and so it is throwing the average weekly sales out. Any way of combatting this? Is there a way of saying "if the cell had to exclude a #REF! error, exclude that result". I'm assuming not...

Thanks again

Chris

**nigelbloomy** · 07-20-2015, 02:59 PM

This would be the same as your sumif:

Please Login or Register  to view this content.

I'm not seeing how your sheet works in my head. Can you post a simple version without any confidential information? If you clikc on "Go Advanced" you can use the paperclip icon to attach a file.

**chrisrye13** · 07-20-2015, 03:12 PM

Originally Posted by nigelbloomy

This would be the same as your sumif:

Please Login or Register  to view this content.

I'm not seeing how your sheet works in my head. Can you post a simple version without any confidential information? If you clikc on "Go Advanced" you can use the paperclip icon to attach a file.

You're right, that formula does work in the same way - thank you!

I have attached a sample of the spreadsheet without any data. So in the cells are external references to other workbooks. As you can see, a lot of those dates are in the future, so they return the #REF! errors. The averages now work a treat, but the average for the weekly total (cell R33) is returning a lower value than it should, because R10 is returning a value for the running total for the weekly sales, which isn't yet a full week. Hopefully this makes sense.

Chris

**nigelbloomy** · 07-20-2015, 04:06 PM

If you add something like this to the totals in R and S:

Please Login or Register  to view this content.

Then you get a "" for any weeks that are not started yet. The "" are not calculated in the average formula in R33.

**chrisrye13** · 07-20-2015, 04:27 PM

Originally Posted by nigelbloomy

If you add something like this to the totals in R and S:

Please Login or Register  to view this content.

Then you get a "" for any weeks that are not started yet. The "" are not calculated in the average formula in R33.

That's returning a #REF! error, probably because of the external references in the cells. But I don't think it'd help anyway. After a Sunday, the weekly total would be the same amount as the Sunday, and therefore a lot less than it will be by the end of the week. It would work if I changed it to =IF(P8=0,"",SUM(D8+F8+H8+J8+L8+N8+P8)) because then it would return "" until the week was finished, as opposed to started (if I understand the formula right) but then, I wouldn't get a running total of the week so far (I'm fussy, I know!) Say for instance on a Wednesday, I want to be able to see the total for the week so far, but then not have the incomplete week be used to calculate the average, as it will throw it off.

Does that make sense/am I understanding correctly?

Chris

**nigelbloomy** · 07-20-2015, 04:57 PM

Does this do what you are looking for?

Please Login or Register  to view this content.

Average keeps factoring in all of the 0 values, averageif doesn't like an array and neither does aggregate. I know this formula looks bad, but I think it gives you what you are looking for.

**chrisrye13** · 07-20-2015, 05:59 PM

Originally Posted by nigelbloomy

Does this do what you are looking for?

Please Login or Register  to view this content.

Average keeps factoring in all of the 0 values, averageif doesn't like an array and neither does aggregate. I know this formula looks bad, but I think it gives you what you are looking for.

I can see that it works on your attachment, but I think the external reference errors are somehow throwing it out. It's strange - the results in the weekly total are the same as in your attachment, but it's returning a result of 0. I have updated an attached my sample with a couple of external references returning errors to show you what I mean.

Chris

**chrisrye13** · 07-20-2015, 06:16 PM

I have attached another sample with the desired result, in case that helps. I'd like the weekly totals to update as the days' sales are updated, but the average weekly sales to only reflect fully completed weeks.

Really appreciate your help!

Chris

**quekbc** · 07-20-2015, 07:28 PM

Somewhat ugly, but it does the trick me thinks.

Formula:

Please Login or Register  to view this content.

This is an array formula, so remember to key in the formula, press and hold CTRL and SHIFT, then hit ENTER.

**chrisrye13** · 07-20-2015, 09:05 PM

Originally Posted by quekbc

Somewhat ugly, but it does the trick me thinks.

Formula:

Please Login or Register  to view this content.

This is an array formula, so remember to key in the formula, press and hold CTRL and SHIFT, then hit ENTER.

You're an absolute hero! That works perfectly! I did get a bit confused as it doesn't adjust the formula when you shift cells up or insert rows etc, but as long as I don't do that, I'll be gravy!

Thanks so much, honestly. I really thought I'd just get told there wasn't a way of having what I wanted. I owe you a beer!

Chris

**quekbc** · 07-20-2015, 09:45 PM

You can try this... instead of testing it against whether or not the rows are even, it only looks into cells that have a date in column C.

Formula:

Please Login or Register  to view this content.

Average even-numbered rows, ignoring #REF! errors

LinkBack

Thread Tools

Rate This Thread

Display

Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Re: Average even-numbered rows, ignoring #REF! errors

Thread Information

Users Browsing this Thread

Similar Threads

Ignoring errors when calculating average

[SOLVED] Ignoring errors

Ignoring errors

Formula to average cell range by month, ignoring blanks or errors

Ignoring errors

Average of the last 3 numbered cells

[SOLVED] Ignoring reference errors

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions