pls help..............simple conditional calculation

[#1]

Hi Experts,

i have tables linked like below.

RSM	Branch No	Branch
Mathew	79	BARKING
	134	BOREHAMWOOD
	23	COLLIERS WOOD
	74	EDMONTON
	823	ENFIELD (UA)
	115	HARROW
	24	HORNSEY
Coles	1	WEMBLEY
Adam	54	ALDERSHOT
	112	BASINGSTOKE
	195	CAMBERLEY
	47	CHERTSEY

Branch	Branch No	Working DAYS
WEMBLEY	1	8.5
MANCHESTER	2	8.5
MILTON KEYNES	18	8.5
SHEFFIELD	19	8.5
READING	20	8.5
SWINDON	21	8.5
LEICESTER	22	8.5
COLLIERS WOOD	23	8.5
HORNSEY	24	8.5
NOTTINGHAM	25	8.5
BURY ST. EDMUNDS	26	8.5
EDINBURGH	27	8.5
HAYES	28	8.5

i have to calculate the working days for RSM.

when i calculated, i m getting the result as below.

RSM Name	Working DAYS
Mathew	127.5
Coles	8.5
Adam	119

this is not the requirement.

i should get the result as

RSM Name	Working DAYS
Mathew	8.5
Coles	8.5
Adam	8.5

the result highlighted in blue is the correct one i should get.

how can i do this in the expression.

pls help................

[sunny_talwar]

The first table and the second table don't have all the matching Branch and Branch No. How do you know Mattew only has 8.5 working days when there is no matching Branch and Branch No. in the second table?? Just trying to understand the logic here.

Best,

Sunny

[sunny_talwar]

and if it is always going to be 8.5 why not just create a chart with RSM as Dimension and 8.5 as your expression?

[#4]

hi,

actually this s a sample data.not exact

each RSM has around 10 branches.

but our working days table is set to branch.(it differs by branch)

for ex. this month till date, we got 8.5 working days.

lets say branch 1 has got 8.5 working days till now,branch 2 has got 8 working days till now,

branch 3 has got 9 working days till now and so on.

i need to calculate the working days for RSM.

How can i do that in pivot table.

pls help.

looking forward to your reply.

[#5]

hi sunindia,

i was trying to share my posts two days back with you. but getting error msg as i m not connected with u.

pls give a follow up to me.

so that it ll be helpful to me plssssss

[sunny_talwar · Accepted solution]

If each RSM has 10 branches and you are trying to do a Sum of working days then you will essentially be summing them the working Days from each of the 10 branches. Do you not want that? May be you want to use an average instead to get what you are looking for. Have you tried average???

Best,

Sunny

[sunny_talwar]

Not sure which post you are talking about???

[buzzy996]

first u can tell to us,how u got ........

Adam

8.5?

as per the above data u wont get that figure,any how!

[#9]

i would like to hav solution for this current post only.

but personally,i would like to ask help from you whenever i get any doubt.

i was trying to send msg at that time but couldnt connect with u. i think the reason is i m following you, but you are not following me.

pls help

[#10]

again, its just a sample data.

some branches have working days 8, some have 8.5, some have 9 till now.

but i need to calculate for RSM, not for branch.

pls help