ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How do I make a formula that recalculates itself every 24 hours? (https://www.excelbanter.com/excel-programming/328741-how-do-i-make-formula-recalculates-itself-every-24-hours.html)

HW

How do I make a formula that recalculates itself every 24 hours?
 
I have a spreadsheet that needs to calculate sales figures for the store &
sales associates both monthly and weekly. I need Excel to recalculate each
day of the month on its own (new info will be keyed in each day). I also need
it to ignore cells untill they are ready to be used. Please help...

Tom Ogilvy

How do I make a formula that recalculates itself every 24 hours?
 
Use formulas that accomplish the detailed requirements you have conveyed.

Formulas update on each calculation.

formulas Like Sum, will not be affected if you specify blank cells in their
range and when you enter data in those cells, they will be included in the
calculation.

Another approach would be to use Pivot Tables with the source data set to a
dynamic range using a defined name

Insert= Name= Define

Name: Table1
Refersto =Offset(Sheet1!$A$1,0,0,countA(Sheet1!$A:$A),10)

as an example

Then use the defined name (range) Table1 as the source for the pivot table.

--
Regards,
Tom Ogilvy


"hw" wrote in message
...
I have a spreadsheet that needs to calculate sales figures for the store &
sales associates both monthly and weekly. I need Excel to recalculate each
day of the month on its own (new info will be keyed in each day). I also

need
it to ignore cells untill they are ready to be used. Please help...




HW

How do I make a formula that recalculates itself every 24 hour
 
Thanks Tom, but I still have a problem, I need a fixed amount of data to be
calculated on a given date. i.e., =SUM(A2+B2) ON 05/04/05, =SUM(A2+B2+C2) ON
05/05/05 and so on... I also need to have a similar formula that calculates a
fixed amt of data daily and finds a percentage of two sums. I have tried to
use OnTime and this doesn't seem to allow for the formula growing each day. I
want sales staff to use this spdsheet and not have to do more than key in
sales figures (which will be added by the formula).

"Tom Ogilvy" wrote:

Use formulas that accomplish the detailed requirements you have conveyed.

Formulas update on each calculation.

formulas Like Sum, will not be affected if you specify blank cells in their
range and when you enter data in those cells, they will be included in the
calculation.

Another approach would be to use Pivot Tables with the source data set to a
dynamic range using a defined name

Insert= Name= Define

Name: Table1
Refersto =Offset(Sheet1!$A$1,0,0,countA(Sheet1!$A:$A),10)

as an example

Then use the defined name (range) Table1 as the source for the pivot table.

--
Regards,
Tom Ogilvy


"hw" wrote in message
...
I have a spreadsheet that needs to calculate sales figures for the store &
sales associates both monthly and weekly. I need Excel to recalculate each
day of the month on its own (new info will be keyed in each day). I also

need
it to ignore cells untill they are ready to be used. Please help...





Tom Ogilvy

How do I make a formula that recalculates itself every 24 hour
 
first
=Sum(A2+B2) is redundant

=Sum(A2:B2) should suffice. =A2+B2 is less flexible although equivalent
for A2 and B2 containing numbers

assuming C2 doesn't get filled in until 05/05/05, on 05/04/05 what is the
difference between

=Sum(A2:B2)
and
=Sum(A2:C2)

There is no difference.

There are no formulas that grow over time they way you show. You could do

=Sum(Offset(A2,0,0,1,Today()-DateValue("05/01/05"))

or if you want it to repeat each week

=Sum(Offset(A2,0,0,1,Weekday(Today()-1))

--
Regards,
Tom Ogilvy



--
Regards,
Tom Ogilvy


"hw" wrote in message
...
Thanks Tom, but I still have a problem, I need a fixed amount of data to

be
calculated on a given date. i.e., =SUM(A2+B2) ON 05/04/05, =SUM(A2+B2+C2)

ON
05/05/05 and so on... I also need to have a similar formula that

calculates a
fixed amt of data daily and finds a percentage of two sums. I have tried

to
use OnTime and this doesn't seem to allow for the formula growing each

day. I
want sales staff to use this spdsheet and not have to do more than key in
sales figures (which will be added by the formula).

"Tom Ogilvy" wrote:

Use formulas that accomplish the detailed requirements you have

conveyed.

Formulas update on each calculation.

formulas Like Sum, will not be affected if you specify blank cells in

their
range and when you enter data in those cells, they will be included in

the
calculation.

Another approach would be to use Pivot Tables with the source data set

to a
dynamic range using a defined name

Insert= Name= Define

Name: Table1
Refersto =Offset(Sheet1!$A$1,0,0,countA(Sheet1!$A:$A),10)

as an example

Then use the defined name (range) Table1 as the source for the pivot

table.

--
Regards,
Tom Ogilvy


"hw" wrote in message
...
I have a spreadsheet that needs to calculate sales figures for the

store &
sales associates both monthly and weekly. I need Excel to recalculate

each
day of the month on its own (new info will be keyed in each day). I

also
need
it to ignore cells untill they are ready to be used. Please help...








All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com