any auto formula changing 1 - 31st everyday & multiply $975

[Guest]

Hi excel users,

I'm trying to work on a simple calculation which i cannot figure out the
formula.

Let's say in this column i have: 1 * $975 = $975

How can i make the "1" automatically changes to 2,3,4,5, and to the end of
the month 30th/31st, so that the the figure will be auto calculated adding
each day:
2 * $975 = $1950

Thanks in advance!!
Regards,
Kelly

 

[Bondi]

Hi Kelly,

Not sure if this is what you are looking for but this will give you the
day of the month:

=DAY(TODAY())

So maybe you can use

=DAY(TODAY())*975

Regards,
Bondi

 

[Norman Jones]

Hi Kelly,

If the data starts in row 2, enter the formula

=(ROW()-1)*975

and drag down as far as required.

If, say, column A contains the dates for each day, try the formula:

=DAY(A2)*975

and, again, drag down as far as the last date.

 

[Roger Govier]

Hi Kelly

Try
=DAY(TODAY())*975

 

[Guest]

Hi Bondi,

Appreciate with the formula a lot, is it possible to add in the word "day /
days" to the number too which is running each day?

because the whole thing suppose to show (1 day x $975), but the formula you
gave is now in separate column. so i need to add the word "day" to show the
meaning.

Best Regards,
Kelly

 

[Bondi]

Hi Kelly,

Maybe you can use something like this:

=DAY(TODAY())&" days x $975 = "&DAY(TODAY())*975

Regrads,
Bondi

 

[Guest]

Yes, this one works out perfectly!!!

Thanks for the help.

 

[Guest]

Hi Bondi,

I wonder if you can also help me, for this formula, is there any way to make
it stop counting the days when it stop on the 30th or 31st of the month?

As at the end of the month, i need the total figure to be there too.

Best regards,
Kelly

 

[Bondi]

Hi Bondi,

I wonder if you can also help me, for this formula, is there any way to make
it stop counting the days when it stop on the 30th or 31st of the month?

As at the end of the month, i need the total figure to be there too.

Best regards,
Kelly

Hi Kelly,

How do you intend to use the formula?
Is it one cell for each month ot the year or shall the same cell be
used for different months?
Can you give an example?

Regards,
Bondi

 

[Roger Govier]

Hi Kelly

I'm not sure what you are trying to achieve, but if you have the
Analysis Toolpak loaded Tools>Addins>Analysis Toolpak, then
=IF(DAY(TODAY())=DAY(EOMONTH(TODAY(),0)),TRUE,FALSE)
will test whether today is the last day of the month.
Maybe you can incorporate this into your formula.

 

[Guest]

Hi there again Bondi,

I've tried to work out the spreadsheet, and I'll try to explain in this
example:

1 day x $975 ("1 day" will stop auto calculating once it reach 30th, last
day of the month) so i will have the total figure of 30 days x $975 = 29250.

and then i'll create another new spreadsheet for the next month starting
from 1 day x $957 again till it reach 30/31st last day of the month and have
a total of 31 days x $975 = 30225.

I hope you get what i mean, as i believe once the formula reaches 31st, it
will roll back to the 1st of a new month again. so i am not able to keep a
soft copy of the last month figure.

Best regards,
Kelly Lim

 

[Bondi]

Hi there again Bondi,

I've tried to work out the spreadsheet, and I'll try to explain in this
example:

1 day x $975 ("1 day" will stop auto calculating once it reach 30th, last
day of the month) so i will have the total figure of 30 days x $975 = 29250.

and then i'll create another new spreadsheet for the next month starting
from 1 day x $957 again till it reach 30/31st last day of the month and have
a total of 31 days x $975 = 30225.

I hope you get what i mean, as i believe once the formula reaches 31st, it
will roll back to the 1st of a new month again. so i am not able to keep a
soft copy of the last month figure.

Best regards,
Kelly Lim

Hi Kelly,

I think we have to tell Excel what month and year (in case of leap
year) we are operating in.

So if you have the month of your sheet in one cell such as July in ths
sheet for July (In this formula it is A2) we can tell that to the
formula. In this formula the year is hardcoded to 2006 but i guess you
can make a cell reference.

This has become a rather long formula and i'm quiet sure that it can be
done in a much nicer and more elegant way but here goes:

=IF(DAY(TODAY())<DAY(EOMONTH(DATEVALUE("01-"&A2&"-2006"),0)),DAY(TODAY())&"
days x $975 =
"&DAY(TODAY())*975,DAY(EOMONTH(DATEVALUE("01-"&A2&"-2006"),0))&" days x
$975 = "&" days x $975 =
"&DAY(EOMONTH(DATEVALUE("01-"&A2&"-2006"),0))*975)

Best of luck

Regards,
Bondi

 

[Guest]

Dear Bondi,

I'm kinda confused on where should i insert your formula. i insert July-2006
in A2 cell and the long formula in A3..but it doesnt works...i;ve got
"#NAME?".

Do you mind to explain more specific to me?

Thanks,
Kelly

 

[Bondi]

Dear Bondi,

I'm kinda confused on where should i insert your formula. i insert July-2006
in A2 cell and the long formula in A3..but it doesnt works...i;ve got
"#NAME?".

Do you mind to explain more specific to me?

Thanks,
Kelly

Hi Kelly,

One reason might be that you do not have the Analysis ToolPak AddIn..
Try to go to Tools -> Add-Ins and here check-mark The Analysis ToolPak
option and click OK.
This should make the formula work.

Otherwise post again and we'lll find another solution.

A little remark is that you should only put the month ("july") in A2
not both month and year (July-06)

Regards,
Bondi

 

[Guest]

Hello Bondi,

I finally get the formula working in the Excel, but i just realized, when i
change the computer date&time to 1st August 06. The formula will still change
to 1 day x $975 and did not stop on the 31st instead, when i also did insert
JULY in cell A2.

Did i do it wrongly? please advise.

Regards,
Kelly

 

[Guest]

Hello Bondi,

I finally get the formula working in the Excel, but i just realized, when i
change the computer date&time to 1st August 06. The formula will still change
to 1 day x $975 and did not stop on the 31st instead, when i also did insert
JULY in cell A2.

Did i do it wrongly? please advise.

Regards,
Kelly