# Remove first cell in a range

#### m5edward

##### New Member
Hi All,

I work for a retailer and would like compute the average weekly sales for each one of our stores.

That said, I want to eliminate the first week of sales from the average because it is always much lower and affects the overall average.

What is the best way to accomplish this?

Below is an example of what I want to do, but in an automated fashion (we have 100s of stores). Column K is just the average of column B to J, while column L is the average without the first week of sales (desired result).

Thanks,

Mark

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
``=AVERAGEIF(B2:J2,">"&MIN(B2:J2))``

Hi Fluff,

Thank you very much for your suggestion.

It appears to be looking for a lowest sales number in the range, and removing that number from the average.

This produces the desired result for most stores, but not all.

Do you have any other suggestions?

Thanks,

Mark

In that case please post some sample data where it is not working.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also you may have missed this
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Book1
1Store NumberStore BannerStore NameStore Ownership TypeStore Centre TypeStore Placement Within CentreStore Size2021-01-092021-01-162021-01-232021-01-302021-02-062021-02-132021-02-202021-02-272021-03-062021-03-132021-03-202021-03-272021-04-032021-04-102021-04-172021-04-242021-05-012021-05-082021-05-152021-05-222021-05-292021-06-052021-06-122021-06-192021-06-262021-07-032021-07-102021-07-172021-07-242021-07-312021-08-072021-08-142021-08-212021-08-282021-09-042021-09-112021-09-182021-09-252021-10-022021-10-092021-10-162021-10-232021-10-302021-11-062021-11-132021-11-202021-11-272021-12-04Total 20212021 Avg Weekly SalesDesired Result
2B1Banner 1CalgaryCorporateStrip CentreTBD1200\$ 10,759\$ 20,343\$ 24,536\$ 27,782\$ 30,775\$ 30,327\$ 31,561\$ 32,980\$ 32,182\$ 37,365\$ 36,595\$ 36,461\$ 37,823\$ 36,809\$ 39,068\$ 35,960\$ 37,341\$ 40,404\$ 38,405\$ 33,064\$ 39,171\$ 36,018\$ 38,226\$ 33,715\$ 36,694\$ 41,253\$ 39,882\$ 34,400\$ 43,955\$ 39,302\$ 37,653\$ 37,200\$ 43,773\$ 39,516\$ 39,491\$ 39,623\$ 38,437\$ 35,542\$ 36,843\$ 33,374\$ 36,792\$ 37,856\$ 34,538\$ 35,030\$ 36,631\$ 1,595,455\$ 35,455\$ 36,016
3B2Banner 1EdmontonFranchiseStrip CentreInline400\$ 4,015\$ 8,303\$ 10,458\$ 12,742\$ 12,683\$ 15,465\$ 21,048\$ 25,422\$ 28,786\$ 33,786\$ 26,577\$ 29,109\$ 29,071\$ 30,012\$ 34,747\$ 33,440\$ 34,469\$ 34,985\$ 38,259\$ 41,544\$ 42,856\$ 45,702\$ 44,551\$ 45,749\$ 42,868\$ 46,824\$ 47,046\$ 43,913\$ 49,288\$ 47,667\$ 50,905\$ 49,255\$ 48,725\$ 49,803\$ 51,718\$ 51,786\$ 49,892\$ 49,342\$ 47,992\$ 53,846\$ 46,730\$ 47,949\$ 47,456\$ 46,118\$ 42,828\$ 1,695,731\$ 37,683\$ 38,448
4B3Banner 1TorontoFranchiseStreetfrontEndcap1450\$ 19,245\$ 1,000\$ 25,755\$ 30,355\$ 34,349\$ 34,878\$ 40,949\$ 43,898\$ 48,308\$ 58,201\$ 62,125\$ 60,437\$ 59,697\$ 58,930\$ 54,774\$ 58,276\$ 58,407\$ 53,283\$ 56,767\$ 57,954\$ 60,848\$ 59,543\$ 62,098\$ 67,025\$ 72,264\$ 72,868\$ 72,986\$ 68,670\$ 74,401\$ 76,394\$ 73,695\$ 76,682\$ 73,894\$ 73,579\$ 76,710\$ 76,652\$ 81,210\$ 78,863\$ 79,118\$ 79,189\$ 79,172\$ 78,484\$ 77,736\$ 76,791\$ 75,673\$ 78,121\$ 73,577\$ 71,767\$ 2,985,597\$ 62,200\$ 63,114
Sheet1
Cell Formulas
RangeFormula
BD2:BD4BD2=SUM(H2:BC2)
BE2:BE4BE2=AVERAGE(H2:BC2)
BF2:BF3BF2=AVERAGE(L2:BC2)
BF4BF4=AVERAGE(I4:BC4)

Hi Fluff,

Thanks for introducing me to this tool.

The first two rows of data are simple. The average starts at 2021-02-06.

The third row is where it gets complicated. The 'averageif' formula you provided me will remove 2021-01-16 from the average when it should be 2021-01-09 that gets removed.

Is there a way to tell Excel "I want the first non-blank cell in the range to be removed from the average"?

Thanks,

Mark

If have still not said what version of Excel you are using.

If have still not said what version of Excel you are using.
Sorry about that. I am using Microsoft 365.

I am using Microsoft 365.
Thanks for that, please don't forget to update you account details to show that.

Excel Formula:
``=AVERAGE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,H2:BC2)&"</m></k>","//m[position()>1]"))``

Replies
0
Views
143
Replies
4
Views
455
Replies
3
Views
254
Replies
8
Views
262
Replies
1
Views
401

1,203,690
Messages
6,056,755
Members
444,889
Latest member
ibbara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back