Extracting Values from Database

[sid1986]

Hi,

I am not sure if this question has been posted before.

I have a sheet of data which has revenue earned by date. Now what i want to do is to compare data day wise for example, I would want to compare revenue earned today, which is wednesday with all the previous wednesday's of the year and Rank today against the year.

 

[JASONRUBALCAVA]

need more info.. and post some sample data

 

[Scuderia]

Hi

If you add a column called "day" with the formula =TEXT(A1,"DDD") where A1 is the first cell of your date field. Then you could pivot your data showing the following fields

Day as a report filter
Date as a row label
Revenue as a value
Then add revenue as a value again & show it as "rank" (say largest to smallest)

This ranks all of your revenue by date. Change the report filter to Wednesday for your required data

 

[sid1986]

Hi

If you add a column called "day" with the formula =TEXT(A1,"DDD") where A1 is the first cell of your date field. Then you could pivot your data showing the following fields

Day as a report filter
Date as a row label
Revenue as a value
Then add revenue as a value again & show it as "rank" (say largest to smallest)

This ranks all of your revenue by date. Change the report filter to Wednesday for your required data

This definitely would work but its again a lengthy process.

I want it to be more dynamic wherein as soon I enter today's i.e wednesday's data, it should automatically match with all the previous wednesdays and rank today's figures with rest of them.

 

[sid1986]

need more info.. and post some sample data

[/IMG]

hope this helps.

 

[sid1986]

need more info.. and post some sample data

need more info.. and post some sample data

	A	B	C	D	E	F
1	Date	Online time(Hrs)	Cust Contact (no's)	Success No.s	Amount	Day
2	02/01/2013	33	106	27	£ 112	Wednesday
3	03/02/2013	44	120	65	£150	Tuesday
4	01/05/2013	22	220	27	£88	Wednesday

<tbody>
</tbody>

This is sample data. Now I want my table to be dynamic and to reflect the ranks as and when i add more data to it. For example if I add date and data for next Wednesday. The formula should look at all the previous wednesdays and rank them.

hope this helps.

 

[JASONRUBALCAVA]

no image is there .. just highlight the cells on your sheet.. give them borders... and copy and paste into here..oops sorry it appeard.. haha..

 

[Scuderia]

Creating the pivot would be a one off then once you have entered todays data... just right click and refresh the pivot & click on wednesday in the report filter

You could also if you prefer keep the "day" column and add 2 more columns

1 Revenue2 =IF(B2=TEXT(MAX($C$2:$C$5000),"DDD"),D2,"") where B = your day column C is your date column and D is your revenue column
2 Rank =IF(ISERROR(RANK.EQ(D2,$E$2:$E$5000)),"",RANK.EQ(D2,$E$2:$E$5000)) where E = Revenue2 column above

This should auto match with the previous wednesdays & rank accordingly

 

[sid1986]

For some reason its getting error and giving me blank cells.

 

[sid1986]

I have just tried this formula seems to have worked but I feel there is something wrong with this but i am not able to find what s wrong =IF(B2=(TEXT(TODAY(),"DDDD")),RANK(J2,J2:J113,0),"")