If Condition

[ad2ad79]

Hi.. right now I have a worksheet in which I Multiply a particular
amount of cell H45 with a no. of days of Month (i.e January contains
31 days. feb containt 28 )

Right now I m doing this: ( =h45*31) for feb ( h45 * 28) etc
manually ...

is this possible with IF condition... like below condition...(i've
type at the bottom of the sheet in blank area of column A JAN, FEB,
MAR, APR, MAY, & in column B I typed 1, 2, 3, 4, 5, 6 ) respectively
in JAN, FEB .. changed Font/text color as white... to disappear...

what i need is:

if a41 ( where the text is written like this: "statement for the
month of July - 2009 for" &g39&)
Month's first three characters are Feb then h45 multiply by 28, if a41
(text lenght three characters are ... then formula continues with
multiplication of 30 and at last the rest will be multiplies with 31

I hope u got my point..

waiting 4 ur quick reply
thanks

 

[Pete_UK]

Suppose your list of months Jan, Feb, Mar, Apr etc occupies A101 to
A112. In column B next to the months you can enter the number of days
for each month, i.e. 31, 28, 31, 30 etc, and then your formula
becomes:

=H45 * VLOOKUP(TEXT(A41,"mmm"),A$101:B$112,2,0)

I'm assuming your date is in A41, although it is a bit confusing what
you have in there.

Hope this helps.

Pete

 

[ad2ad79]

Suppose your list of months Jan, Feb, Mar, Apr etc occupies A101 to
A112. In column B next to the months you can enter the number of days
for each month, i.e. 31, 28, 31, 30 etc, and then your formula
becomes:

=H45 * VLOOKUP(TEXT(A41,"mmm"),A$101:B$112,2,0)

I'm assuming your date is in A41, although it is a bit confusing what
you have in there.

Hope this helps.

Pete









- Show quoted text -

I m sorry it doesn't work... The main problem is A41.. Actually In the
cell A41.. the text is written as under
="Total Expenses for "&H15&" Persons for the Month of July-2009"

thanks.. n hope now u got what i wana explain u.

 

[Pete_UK]

So how does that "July-2009" get into the text in A41? Do you type it
each month? It would be better to get it from a date somewhere in the
sheet (e.g. in Z1) and to use it within the formula like this:

="Total Expenses for "&H15&" Persons for the Month of "&TEXT(Z1,"mmmm-
yyyy")

Z1 can contain any date in the month and year you are interested in,
i.e. 01/07/09 or 31/07/09 (UK format date), or anything in between.
Then in my earlier formula you could use Z1 instead of A41 like this:

=H45 * VLOOKUP(TEXT(Z$1,"mmm"),A$101:B$112,2,0)

Hope this helps.

Pete

 

[Shane Devenshire]

Start by modifying the formula in A41 to read:

="Total Expenses for "&H15&" Persons for the Month of "&TEXT(A1,"mmmm-yyyy")

Second enter the in cell A1 (you can use any cell you want, just references
it in the formula above.) Enter it as an Excel legal date, any day in the
month is fine, for example if you enter it as July-2009 then Excel will
consider it a date and display Jul-09, which you can format any way you
choose.

For your calculation that you are now doing that reads =H45*31 use

=EOMONTH(A1,0)*H45

Note that in 2003 or earlier you need to attach the ATP to use the EOMONTH
function. Do that by choosing Tools, Add-ins, and checking Analysis ToolPak.

 

[ad2ad79]

Suppose your list of months Jan, Feb, Mar, Apr etc occupies A101 to
A112. In column B next to the months you can enter the number of days
for each month, i.e. 31, 28, 31, 30 etc, and then your formula
becomes:

=H45 * VLOOKUP(TEXT(A41,"mmm"),A$101:B$112,2,0)

I'm assuming your date is in A41, although it is a bit confusing what
you have in there.

Hope this helps.

Pete









- Show quoted text -

Thanks Pete......b
But it's very confusing....... if u really wana c the file... let me
know how can i send/email or share u?

 

[ad2ad79]

Start by modifying the formula in A41 to read:

="Total Expenses for "&H15&" Persons for the Month of "&TEXT(A1,"mmmm-yyyy")

Second enter the in cell A1 (you can use any cell you want, just references
it in the formula above.)  Enter it as an Excel legal date, any day in the
month is fine, for example if you enter it as July-2009 then Excel will
consider it a date and display Jul-09, which you can format any way you
choose.

For your calculation that you are now doing that reads =H45*31 use

=EOMONTH(A1,0)*H45

Note that in 2003 or earlier you need to attach the ATP to use the EOMONTH
function.  Do that by choosing Tools, Add-ins, and checking Analysis ToolPak.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire







- Show quoted text -

sorry... it doesn't work result is not coming correct after putting
formula...

 

[Pete_UK]

You may send it to:

pashurst <at> auditel.net

(change the obvious). Please send it as .xls not .xlsx, and if it is
large then zip it first.

Pete

 

[ad2ad79]

You may send it to:

pashurst <at> auditel.net

(change the obvious). Please send it as .xls not .xlsx, and if it is
large then zip it first.

Pete





- Show quoted text -

Just sent u by email... pls chk ur inbox

 

[Pete_UK]

File amended and sent back to you.

I didn't see a table of months, as you described in your first post,so
I amended the formula in F47 to this:

=$H$45/IF($B$2="FEB",28,IF(OR($B$2="APR",$B$2="JUN",$B$2="SEP",$B
$2="NOV"),30,31))

Hope this helps.

Pete

 

[ad2ad79]

File amended and sent back to you.

I didn't see a table of months, as you described in your first post,so
I amended the formula in F47 to this:

 =$H$45/IF($B$2="FEB",28,IF(OR($B$2="APR",$B$2="JUN",$B$2="SEP",$B
$2="NOV"),30,31))

Hope this helps.

Pete





- Show quoted text -

Thanks I got the file and understand but... there were some macros....
facs*.* and another file which was password protected.... that's y I
just copied formula and deleted file completely.... by the
way............. I sent the the file with only 2 sheet tabs... but u
resent me with rest of the sheets which I deleted b4 sent to u.. I m
afraid there were some machors used which may harmful for my system...
that's y i deleted the file with both files....

waiting 4 ur comments regarding both macros/files... and recovered
sheet tabs...

 

[Pete_UK]

I asked you to send it as .xls, but it came as .xlsx. As I don't have
XL2007, I opened it using OpenOffice, then saved it as an .xls file,
so that I could work on it in XL2000. I didn't put any macros in it,
nor was I warned about any when I opened it, so I can only conclude
that Open Office did something (and was able to restore your deleted
sheets !!).

Anyway, I showed the amended formula in an earlier post.

Pete

 

[Pete_UK]

Just checked again, and definitely no macros in the file I sent to
you.

Pete

 

[ad2ad79]

Just checked again, and definitely no macros in the file I sent to
you.

Pete





- Show quoted text -

Thanks pete.. 4 confidential and satisfaction.... Now could u pls
again sent that file ... b coz i was afraid and deleted previous file
quickly... Hope u understand... I really don't know... that's y....
Thanks once again.

 

[Pete_UK]

File re-sent to you.

Pete

Thanks pete.. 4 confidential and satisfaction.... Now could u pls
again sent that file ... b coz i was afraid and deleted previous file
quickly... Hope u understand... I really don't know... that's y....
Thanks once again.- Hide quoted text -

- Show quoted text -