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

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

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

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.


---
Regards,
Norman


"Kelly Lim" <Kelly [email protected]> wrote in message
news:[email protected]...
> 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
>

Hi Kelly

Try
=DAY(TODAY())*975


--
Regards

Roger Govier


"Kelly Lim" <Kelly [email protected]> wrote in message
news:[email protected]...
> 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
>

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" wrote:

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

Hi Kelly,

Maybe you can use something like this:

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

Regrads,
Bondi

Yes, this one works out perfectly!!!

Thanks for the help.

"Bondi" wrote:

>
> Hi Kelly,
>
> Maybe you can use something like this:
>
> =DAY(TODAY())&" days x $975 = "&DAY(TODAY())*975
>
> Regrads,
> 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

"Bondi" wrote:

>
> Hi Kelly,
>
> Maybe you can use something like this:
>
> =DAY(TODAY())&" days x $975 = "&DAY(TODAY())*975
>
> Regrads,
> Bondi
>
>

Kelly Lim wrote:
> 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

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

Roger Govier


"Kelly Lim" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>>
>> Hi Kelly,
>>
>> Maybe you can use something like this:
>>
>> =DAY(TODAY())&" days x $975 = "&DAY(TODAY())*975
>>
>> Regrads,
>> 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

> Kelly Lim wrote:
> > 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
>
>

Kelly Lim wrote:
> 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
>
> > Kelly Lim wrote:
> > > 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
> >
> >

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

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

Kelly Lim wrote:
> 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,
> >
> > 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
> >
> >

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

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

"Bondi" wrote:

>
> Kelly Lim wrote:
> > 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,
> > >
> > > 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
> > >
> > >
>
> 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
>
>

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