Multiple lookup

  • Thread starter Thread starter Denz
  • Start date Start date
D

Denz

Hoping someone can help.

I have a spreadsheet that has weekly sales figures

A B C D E F ->
Wk 1 Wk1 Wk1 Wk2 Wk2 Wk2->wk52
Sales Margin % Sales Margin %
John 3000 2000 33 2000 1000 50
Alan 2500 1500 40 4000 3000 25
Etc

In another spreadsheet I would like to reflect the total
sales, total margins and the average sales, average
margins. I do not need the % as I can do this with a
simple simple in this sheet.

How do I total all weeks and the averages for each person
bearing in mind that the average needs only to total
weeks where data is entered.

Help

D
 
Denz said:
Hoping someone can help.

I have a spreadsheet that has weekly sales figures

A B C D E F ->
Wk 1 Wk1 Wk1 Wk2 Wk2 Wk2->wk52
Sales Margin % Sales Margin %
John 3000 2000 33 2000 1000 50
Alan 2500 1500 40 4000 3000 25
Etc

In another spreadsheet I would like to reflect the total
sales, total margins and the average sales, average
margins. I do not need the % as I can do this with a
simple simple in this sheet.

How do I total all weeks and the averages for each person
bearing in mind that the average needs only to total
weeks where data is entered.

Help
Click to expand...

Hi
though this can be done with formulas I would use pivot tables instead.
In addition your spreadsheet layout will make it quite complicated to
use formulas. I would add the week information as a seperate column.
This will make it much easier to use pivot tables

Frank
 
As has been suggested, use a Pivot Table, BUT, your data is not the optimal
layout for a Pivot table, and would be better off in Database format. This
however can be gotten around using a nice little trick that you will find on
John Walkenbach's site:-

http://j-walk.com/ss/excel/usertips/tip068.htm

Once you have done this you will then be able to make umpteen different reports
very very easily indeed. If you have never used Pivot tables then head on over
to Debra Dalgliesh's great intro, and you will soon be up and running. :-)

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
 
Denz said:
I have a spreadsheet that has weekly sales figures

A B C D E F ->
Wk 1 Wk1 Wk1 Wk2 Wk2 Wk2->wk52
Sales Margin % Sales Margin %
John 3000 2000 33 2000 1000 50
Alan 2500 1500 40 4000 3000 25
Etc
Click to expand...

I'll assume the top row of letters isn't part of your data, and besides col
A should be over the names rather than week 1 sales. For my own simplicity,
I'll assume the entire range is named TBL.
In another spreadsheet I would like to reflect the total
sales, total margins and the average sales, average
margins. I do not need the % as I can do this with a
simple simple in this sheet.

How do I total all weeks and the averages for each person
bearing in mind that the average needs only to total
weeks where data is entered.
Click to expand...

If you have names in col A in the other worksheet starting in row 2, so John
is in cell A2, try these formulas.

B2: total sales
=SUMPRODUCT((INDEX(TBL,0,1)=A2)*(INDEX(TBL,2,0)="Sales"),TBL)

C2: total margin
=SUMPRODUCT((INDEX(TBL,0,1)=A2)*(INDEX(TBL,2,0)="Margin"),TBL)

D2: average sales
=B7/SUMPRODUCT((INDEX(TBL,0,1)=A7)*(INDEX(TBL,2,0)="Sales")*ISNUMBER(TBL))

E2: average margin
=B7/SUMPRODUCT((INDEX(TBL,0,1)=A7)*(INDEX(TBL,2,0)="Margin")*ISNUMBER(TBL))

Given your data layout, I think formulas would work much easier than pivot
tables.
 
Harlan Grove said:
D2: average sales
=B7/SUMPRODUCT((INDEX(TBL,0,1)=A7)*(INDEX(TBL,2,0)="Sales")*ISNUMBER(TBL))

E2: average margin
=B7/SUMPRODUCT((INDEX(TBL,0,1)=A7)*(INDEX(TBL,2,0)="Margin")*ISNUMBER(TBL))
Click to expand...
....

Oops! Make those

D2: average sales
=B2/SUMPRODUCT((INDEX(TBL,0,1)=A2)*(INDEX(TBL,2,0)="Sales")*ISNUMBER(TBL))

E2: average margin
=B2/SUMPRODUCT((INDEX(TBL,0,1)=A2)*(INDEX(TBL,2,0)="Margin")*ISNUMBER(TBL))
 
Back
Top