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 wrote in message
...
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 wrote in message
...
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 ToolsAddinsAnalysis Toolpak, then
=IF(DAY(TODAY())=DAY(EOMONTH(TODAY(),0)),TRUE,FALS E)
will test whether today is the last day of the month.
Maybe you can incorporate this into your formula.
--
Regards

Roger Govier


"Kelly Lim" wrote in message
...
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