3 Questions
 

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
--


--

3 Questions
 

Q3:

=TEXT(B17,"dddd")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

<Django Cat wrote in message o.uk...
| 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
| --
|
|
| --
|

3 Questions
 

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

"Django Cat" wrote:

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
--


--

3 Questions
 

On Sat, 10 Nov 2007 08:35:30 -0600, "Django Cat" < wrote:

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?

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?

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).


=text(b17,"dddd")

or =b17 and custom format the cell as dddd


--ron

3 Questions
 

On Sat, 10 Nov 2007 06:58:01 -0800, pinmaster
wrote:

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

=MAX(D1:D100)

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

3 Questions
 

On Sat, 10 Nov 2007 08:35:30 -0600, "Django Cat" < wrote:

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?

Simpler solution for this question:

=LOOKUP(9.9E+307,Total)

will return the last entry in the Total range.

--ron

3 Questions
 

On Sat, 10 Nov 2007 08:35:30 -0600, "Django Cat" < wrote:

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?


=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

3 Questions
 

Hi,

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?

Simpler solution for this question:

=LOOKUP(9.9E+307,Total)


Looks like we overlooked the simplest of all:

=SUM(C1:C100)

Regards!
Jean-Guy

"Ron Rosenfeld" wrote:

On Sat, 10 Nov 2007 08:35:30 -0600, "Django Cat" < wrote:

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?

Simpler solution for this question:

=LOOKUP(9.9E+307,Total)

will return the last entry in the Total range.

--ron

3 Questions
 

Niek Otten said:

=TEXT(B17,"dddd")


That works, TVM.
DC

--

3 Questions
 

Ron Rosenfeld said:

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


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

--

3 Questions
 

On Sat, 10 Nov 2007 08:41:01 -0800, pinmaster
wrote:

Looks like we overlooked the simplest of all:

=SUM(C1:C100)

Regards!
Jean-Guy

Duh.

<sound of slapping forehead
--ron

3 Questions
 

Ron Rosenfeld said:

Simpler solution for this question:

=LOOKUP(9.9E+307,Total)

will return the last entry in the Total range.


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

--

3 Questions
 

pinmaster said:

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


Thanks JG - that seems to work - I'll know tomorrow for sure.
DC

--

3 Questions
 

Ron Rosenfeld said:

Looks like we overlooked the simplest of all:

=SUM(C1:C100)

Regards!
Jean-Guy

Duh.


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

Cheers
DC

--

3 Questions
 

Ron Rosenfeld said:

On Sat, 10 Nov 2007 08:35:30 -0600, "Django Cat" < wrote:

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?

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?

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



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

3 Questions
 

On Sun, 11 Nov 2007 16:31:11 -0600, "Django Cat" < wrote:

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


=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

3 Questions
 

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)


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

--