excel problem

  broggs 16:30 24 Apr 05
Locked

hello....I know nothing much about excel so I would like a bit of advice.
I have been given a simple report which is in excel.
column A is all the product codes ranging from the lowest to the highest...cc0001 to cc9993.Column B is the product description,C is the price and D is the quantitiy sold for that particular day.
Depending on what product is sold during the period that the report covers, the number of rows are duplicated.for example if cc0001 is sold on ten of the days I have ten rows with cc0001,If cc0002 sells on twenty of the days I have twenty rows and so on.The only data that does change in these multiple rows is in column D which shows how many where sold on that particular day.
What I need to do is to put 2 blank rows between each batch of codes as it changes to the next code down e.g cc0001 two blank rows then cc0002 two blank rows cc0003 and so on down to the last code.
In the first of the two blank rows I want to auto sum the totals in colomn D for each of the codes.
In the second I want to divide the total of the first blank row by 6.
Does this make sense? if so can anyone help me please.

  VoG II 16:37 24 Apr 05

Thi first part is straightforward. Make sure that you have headings (Part No etc)at the top of your data. Select all the data including the headings, choose Data/Subtotals and follow the prompts.

I'm not quite sure what you mean by the dividing by 6 part.

  broggs 16:46 24 Apr 05

basically the report is for twelve weeks sales,so I want to find the total sold over twelve weeks and then divide that total by 6 to give me average two weeks sales.Why I need to do this is to calculate the stock we need to carry over a two week period.

  VoG II 17:01 24 Apr 05

The easiest way to do this is to place the (subtotal/6) values in Column E. Otherwise it would be laborious to add the extra rows/totals by hand and a pain to code in Visual Basic for Applications as well.

Create your subtotals as above. Then in E2 enter the formula:

=IF(RIGHT(A2,5)="Total",D2/6,"")

Then click in E2, hover the cursor over the bottom right if E2 until the cursor turns into a + (the Fill Tool). Hold down the left mouse button and drag down as far down the sheet as needed. This will display (subtotal/6) only for those rows that contain a subtotal.

  broggs 18:01 24 Apr 05

thanks VoG™ I'll try this .you are indeed a star.

  broggs 19:03 24 Apr 05

The easiest way to do this is to place the (subtotal/6) values in Column E.

how would I do this?

  VoG II 19:05 24 Apr 05

As I explained in my post 17:01.

  broggs 19:11 24 Apr 05

Create your subtotals as above
sorry Vog ...a bit thick here

  VoG II 19:22 24 Apr 05

Make sure that you have headings for columns A-D (Part No, Description etc.). Then select the whole table, on the Data Menu select Subtotals. Excel will then guide you as to what to total and so on. TYour sheet will now display subtotals.

Having done that, then enter this formula in E2

=IF(RIGHT(A2,5)="Total",D2/6,"")

and copy it down (as explained in my 17:01 post).

Mock up:

click here

  Noldi 19:38 24 Apr 05

You could use a autofilter then show the total of what you have filtered. Im sorry I only use Excel in German so I dont remember the correct formula in English I think its called partial results. maybe VOG can enliten you a bit better because he seems to understand what u are trying to do.

  broggs 20:28 24 Apr 05

thanks vog

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…