Date in Text Format - Average to be plotted.

[thilag]

Hello Friends

Please find the attached file.

Date is in text format, but i want to calculate the average for the given month in yellow cells.

Thanks in advance

thilag

[thilag]

Hello Friends

Please find the attached file.

In that file the day in text format and values are plotted, i want to calculate the day average by formula.

thanks in advance.

thilag

[cbatrody]

Hi,

Please see the attached file.

Regards,
Chandra

[Fotis1991]

Hello

Please Login or Register  to view this content.

[:) Sixthsense :)]

In E1 Cell

=SUMPRODUCT((TEXT($A$1:$A$16,"MMM-YY")=TEXT(D1,"MMM-YY"))*$B$1:$B$16)/SUMPRODUCT(--(TEXT($A$1:$A$16,"MMM-YY")=TEXT(D1,"MMM-YY")))

Drag it down...

[cbatrody]

Hi,

I have updated the formula in the attached file.

Regards,
Chandra

[sweep]

Or if you prefer an array

=AVERAGE(IF((VALUE(MID($A$1:$A$16,4,2))=MONTH(D1))*(VALUE(20&RIGHT($A$1:$A$16,2))=YEAR(D1)),$B$1:$B$16,""))

[benishiryo]

hi thilag. if your computer date settings is DMY, then it would be easier. assuming you don't wish to convert the text & simply use formulas, sat would be:

Please Login or Register  to view this content.

or:

Please Login or Register  to view this content.

this will of course fail in computers with date settings as m/d/y. then you would have to use the LEFT, MID & RIGHT like you did.

[thilag]

Hello cbatrody, Hello Fotis1991, Hello Sixth Sense, Hello Sweep

Thanks to you for your immediate reply.

hello cbatrody
You used helper cells.

hello Fotis1991
I tried tour formula as Non Array and array formula.
It shows #VALUE! in both cases

hello Sixth Sense
your formula shows 90 (for 40 Feb-14), 140 (For 100 Mar-14) and #DIV/0! (for 145 Apr-14)

hello sweep

Your formula works very fine.

[Fotis1991]

4 people in 2 diferent(your owns thread) try to help you!!

No respect to their effords and their time, posting duplicate threads(and not even a feed back untill now)!

Speaking seriously i tell you that next time that you'll do this, you'll be a banned user!!

I'll merge the 2 threads now!

[samba_ravi]

=SUM(INDEX((TEXT($A$1:$A$100,"dddd")=$F5)*($B$1:$B$100),0))/SUM(INDEX((TEXT($A$1:$A$100,"dddd")=$F5)*1,0))
try this formula

[Fotis1991]

And my formula works ok.....

[cbatrody]

Without the helper column.

[thilag]

Hello Fortis1991
I really surprised about your merging of the 2 threads.

My two treads are entirely different

1) is calculating average for the given months from the dates (in text Format) and their values.
2) is calculating average for the all the days (Sunday to Saturday) from the dates (in text Format) and their values.

Given Month & all days (Sunday to Saturday) are different then obviously I have to post 2 threads according to forum rules.
And I did it even it is little bit difficult to post 2 post instead making it in a single post.

thilag

[thilag]

Hello Friends

Please refer the attached file.

If we put the date in Column A in Text Format (Any time adding at bottom) then the values for the given days should be updated in the Cells G1 to G7 (Corrsponding days are given in F1 to F7.

thanks

thlag