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


--

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

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


--

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

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

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

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

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

Niek Otten said:

=TEXT(B17,"dddd")


That works, TVM.
DC

--

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

--

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

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.

--

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

--

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

--

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

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

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

--