3 Questions

D

Django Cat

Hi All

I'd be greatful for further help from the assembled Excel Experts with these three points:-

1. I'm currently keeping a running sales worksheet. Column A, has the date, B transaction details and C money taken. Column D keeps a running total - previous row's total plus this row's C. All straightforward stuff so far. But I want to keep a reference to the current rolling total on a summary worksheet - whatever is the bottom entry value in Column D. Is there any way of doing this, when the entry goes down one line for every new transaction?

2. I'd like to add a further column that keeps a running sales figure for the week. To do this it needs to check back to locate a date 7 days before the current date, and then add all the Column C figures up to the current transaction. There could be any number of transactions on any given date. Is this possible?

3. I've just hacked together the following formula to display the day name for any given date:

=IF(WEEKDAY(B17)=7, "Saturday",IF(WEEKDAY(B17)=6, "Friday",IF(WEEKDAY(B17)=5, "Thursday",IF(WEEKDAY(B17)=4, "Wednesday",IF(WEEKDAY(B17)=3, "Tuesday",IF(WEEKDAY(B17)=2, "Monday", IF(WEEKDAY(B17)=1, "Sunday")))))))

(Here column B has transacation dates) Surely there's an easier way of doing this? (I think this only came up recently in the group - apologies if so, I can't find it).

Many thanks

Django Cat
 
N

Niek Otten

Q3:

=TEXT(B17,"dddd")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

<Django Cat> wrote in message | Hi All
|
| I'd be greatful for further help from the assembled Excel Experts with these three points:-
|
| 1. I'm currently keeping a running sales worksheet. Column A, has the date, B transaction details and C money taken. Column D
keeps a running total - previous row's total plus this row's C. All straightforward stuff so far. But I want to keep a reference
to the current rolling total on a summary worksheet - whatever is the bottom entry value in Column D. Is there any way of doing
this, when the entry goes down one line for every new transaction?
|
| 2. I'd like to add a further column that keeps a running sales figure for the week. To do this it needs to check back to
locate a date 7 days before the current date, and then add all the Column C figures up to the current transaction. There could be
any number of transactions on any given date. Is this possible?
|
| 3. I've just hacked together the following formula to display the day name for any given date:
|
| =IF(WEEKDAY(B17)=7, "Saturday",IF(WEEKDAY(B17)=6, "Friday",IF(WEEKDAY(B17)=5, "Thursday",IF(WEEKDAY(B17)=4,
"Wednesday",IF(WEEKDAY(B17)=3, "Tuesday",IF(WEEKDAY(B17)=2, "Monday", IF(WEEKDAY(B17)=1, "Sunday")))))))
|
| (Here column B has transacation dates) Surely there's an easier way of doing this? (I think this only came up recently in the
group - apologies if so, I can't find it).
|
| Many thanks
|
| Django Cat
| --
|
|
| --
|
 
G

Guest

Hi,
I'm not an expert but maybe something along these lines:

Let's start with the last one:

=TEXT(B17,"dddd")

second part, maybe something like this:

=SUMPRODUCT((A1:A100=<TODAY())*(A1:A100>=TODAY()-7),C1:C100)

first part, since the last entry will the largest then you can use:

=MAX(D1:D100)

HTH
Jean-Guy
 
R

Ron Rosenfeld

Hi All

I'd be greatful for further help from the assembled Excel Experts with these three points:-

1. I'm currently keeping a running sales worksheet. Column A, has the date, B transaction details and C money taken. Column D keeps a running total - previous row's total plus this row's C. All straightforward stuff so far. But I want to keep a reference to the current rolling total on a summary worksheet - whatever is the bottom entry value in Column D. Is there any way of doing this, when the entry goes down one line for every new transaction?
Click to expand...

With your columns being named ranges (e.g. Date | Detail | Money | Total

you can use this **array-entered** formula:

=INDEX(Total,1-ROW(Total)+MAX(ISNUMBER(Total)*ROW(Total)))

To **array-enter** a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula if you did it correctly.

2. I'd like to add a further column that keeps a running sales figure for the week. To do this it needs to check back to locate a date 7 days before the current date, and then add all the Column C figures up to the current transaction. There could be any number of transactions on any given date. Is this possible?
Click to expand...

Do you mean for the current week or for the previous seven days?

3. I've just hacked together the following formula to display the day name for any given date:

=IF(WEEKDAY(B17)=7, "Saturday",IF(WEEKDAY(B17)=6, "Friday",IF(WEEKDAY(B17)=5, "Thursday",IF(WEEKDAY(B17)=4, "Wednesday",IF(WEEKDAY(B17)=3, "Tuesday",IF(WEEKDAY(B17)=2, "Monday", IF(WEEKDAY(B17)=1, "Sunday")))))))

(Here column B has transacation dates) Surely there's an easier way of doing this? (I think this only came up recently in the group - apologies if so, I can't find it).
Click to expand...

=text(b17,"dddd")

or =b17 and custom format the cell as dddd


--ron
 
R

Ron Rosenfeld

first part, since the last entry will the largest then you can use:

=MAX(D1:D100)
Click to expand...

That assumes that no "negative" sales numbers are ever entered.
--ron
 
R

Ron Rosenfeld

Hi All

I'd be greatful for further help from the assembled Excel Experts with these three points:-

1. I'm currently keeping a running sales worksheet. Column A, has the date, B transaction details and C money taken. Column D keeps a running total - previous row's total plus this row's C. All straightforward stuff so far. But I want to keep a reference to the current rolling total on a summary worksheet - whatever is the bottom entry value in Column D. Is there any way of doing this, when the entry goes down one line for every new transaction?
Click to expand...

Simpler solution for this question:

=LOOKUP(9.9E+307,Total)

will return the last entry in the Total range.

--ron
 
R

Ron Rosenfeld

2. I'd like to add a further column that keeps a running sales figure for the week. To do this it needs to check back to locate a date 7 days before the current date, and then add all the Column C figures up to the current transaction. There could be any number of transactions on any given date. Is this possible?
Click to expand...


=SUMIF(Date,">="&A10-7,Money)-
SUMIF(Date,">"&A10,Money)

will return transactions during the last 7 days prior to (and including) the
Date in A10.

If you need the current week, and Day 1 of the week is Sunday, then:

=SUMIF(Date,">"&A10-WEEKDAY(A10),Money)-
SUMIF(Date,">"&A10,Money)
--ron
 
D

Django Cat

Ron Rosenfeld said:
That assumes that no "negative" sales numbers are ever entered.
--ron
Click to expand...

They won't be - it's recording the actual cash taken when a transaction happens.
DC

--
 
D

Django Cat

Ron Rosenfeld said:
Simpler solution for this question:

=LOOKUP(9.9E+307,Total)

will return the last entry in the Total range.
Click to expand...

Yes, that works fine and is incredibly helpful - many thanks, Ron.

--
 
D

Django Cat

Ron Rosenfeld said:

Ah. Oh well, thanks both for bringing the LOOKUP function to my attention and reminding me about ranges.

Cheers
DC

--
 
D

Django Cat

Ron Rosenfeld said:
With your columns being named ranges (e.g. Date | Detail | Money | Total

you can use this **array-entered** formula:

=INDEX(Total,1-ROW(Total)+MAX(ISNUMBER(Total)*ROW(Total)))

To **array-enter** a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula if you did it correctly.



Do you mean for the current week or for the previous seven days?
Click to expand...


The previous 7 days - I'd quite like to look at alternative ways of doing this...
DC
--
 
R

Ron Rosenfeld

The previous 7 days - I'd quite like to look at alternative ways of doing this...
DC
Click to expand...


=SUMIF(Date,">="&A10-7,Money)-
SUMIF(Date,">"&A10,Money)

will return transactions during the last 7 days prior to (and including) the
Date in A10.

If you need the current week, and Day 1 of the week is Sunday, then:

=SUMIF(Date,">"&A10-WEEKDAY(A10),Money)-
SUMIF(Date,">"&A10,Money)
--ron
 
D

Django Cat

Ron Rosenfeld said:
=SUMIF(Date,">="&A10-7,Money)-
SUMIF(Date,">"&A10,Money)

will return transactions during the last 7 days prior to (and including) the
Date in A10.

If you need the current week, and Day 1 of the week is Sunday, then:

=SUMIF(Date,">"&A10-WEEKDAY(A10),Money)-
SUMIF(Date,">"&A10,Money)
Click to expand...

Thanks, I'll try that - JG's formula isn't working...
DC

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change date to Day Name 1
Help with Formula... 1
lookup? Index? match? formula 4
Find number of weekdays and wekend days given a total number of da 7
How to get an Excel function to recognise text 1
excel totaling wrong 5
How to direct to a cell from text 3
If formulations 2

Top