[mso] Re: an EXCEL question
- From: "Ray Blake" <ray@xxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Sat, 4 Oct 2003 17:46:02 +0100
Well, starting again is certainly an option worth considering,
particularly since I think that way we can do it without any VBA or
macros at all.
There's no reason why you couldn't put all your sales data in a list on
one sheet, which would look like this:
A B C D E F
G
Date Customer Item No soldItem price Total
cost Tax
01-02-03 Smith Box 1 £20.00 £20.00
£2.00
01-03-03 Jones Box 2 £20.00 £40.00
£4.00
18-09-03 Smith Bag 4 £10.00 £40.00
£4.00
18-09-03 White Box 8 £20.00 £160.00
£16.00
04-10-03 Jones Bag 16 £11.00 £176.00
£17.60
04-10-03 Smith Box 32 £22.00 £704.00
£70.40
I'm going to get you to name some ranges next. Just hit CTRL+F3, and
define the following ranges:
Dates =A2:A1000
Customer = B2:B1000
Item = C2:C1000
NoSold = D2:D1000
ItemPrice = E2:E1000
TotalCost = F2:F1000
Tax = G2:G1000
Let's assume all your totals are based on column D, the number sold.
I would use either a D-function or an array function to work out weekly
and running totals. Here, I'll use arrays for simplicity. Put all these
formulae on a different sheet, or over to the right, past column G at
any rate.
The total amount sold to date to a customer called 'Smith' would read:
=SUMIF(Customer,"Smith",NoSold)
No arrays yet, but that's a simple one. Let's narrow it down to the
number of items sold to Smith in the last seven days:
{=SUM(IF(Customer="Smith",IF(Date>=(TODAY()-7),NoSold,0),0))}
Note that the curly brackets can't be typed in. They denote that you're
making this an array function. You get them by typing the rest of the
formula, then rather than just pressing ENTER, you hold SHIFT and CTRL
down together while pressing ENTER.
Once you get the hang of this, you can add multiple conditions to have
lots of fun! For instance, want to know how boxes you sold in the first
10 days of any month so far? Easy:
{=SUM(IF(DAY(Date)<=10,IF(Item="Box",NoSold,0),0))}
Or what if you wanted to know how much Smith had spent with you just on
boxes this year?
{=SUM(IF(Customer="Smith",IF(Item="Box",IF(YEAR(Date)=YEAR(TODAY()),Tota
lCost,0),0),0))}
Now then. Remember earlier when we defined the ranges? We set them to
look at the first 999 lines only. From what you're saying, you might go
beyond this, particularly if you just keep adding data all year.
What we'll do is go back and change the range definitions so we have
dynamic ranges, ones that will automatically stretch to cater for
however many lines of data we add as we go.
CTRL+F3 will get you back to the range names dialog. You need to change
the definition for the range called Date to the following:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$60000),1)
So long as you don't go over 60,000 lines of data, this will keep the
range properly defined for you. Now do the same thing for the other
named ranges; all you have to change are the column letters each time.
Hopefully, that's given you enough pointers to be getting on with for
now. Shout if you want more information on any of this.
Ray
_____________
This email is from Ray Blake, Head of Software Design, GR Business
Process Solutions. It is confidential and intended for the addressee
only. The contents are private and may be legally privileged. If you
receive this email in error we would be grateful if you would advise the
sender and delete the email from your system.
For more information on the services that we offer please visit us at
our website: - www.grbps.com
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Joyce West
Sent: 04 October 2003 15:50
To: mso@xxxxxxxxxxxxx
Subject: [mso] an EXCEL question
Hi Ray.
The daily sales are generated by direct entry.
The are many customers so there are quite alot of
entries.
They are collectd and total at the right hand side of
the daily sales with eg SUM(C3:23)
These are totalled at the bottom of the section to
obviously give a total.
On the same sheet and below the daily sales record
there is another section which reads each individual
customers weekly sales figure ie =J3 and so on.
To the right of this section is the accrued total for
the yearly sales to which I wish to add the weekly
sales week on week, but the problem is that if the
yearly sales are generated from the weekly sales, and
the weekly sales are overwritten on the weekly sales
sheet, then the values for past weeks are lost unless
I can somehow fix them to remain in the yearly to date
column.
The only way I could think of doing it was to have a
weekly sales sheet for each week of the year, reading
into the yearly sales figure.
I think I have got myself into a mess somewhat inamuch
that I would also like to have a chart record of each
customer sales trend of the yearly period.
Perhaps it might be better to start again with a
different concept. What do you think?
Many thanks for your perseverance.
Norman West
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
