Divide an expression by the total of the expression

[qliklizzy]

Hi

i have an expression called Extra which diplays a count of the difference between beds used and allocated by specialty

i need to be able to divide the extra for each specialty by the total extra to work out the probability for each specialty.

which i though i could just do [Extra] / sum([Extra] but this does not work.

in excel i have the workings below

I have left the formula in C26 to show the proability workings needed

once this is calculated i need to work out how many beds to allocate based on the specialties probabilty.

the formula is the same for each specialty apart from ELM - the if statement refers to 1/3 rather than 1/6

is this doable in an expession ?

thanks liz

24	A	B	C	D
25	Spec	Extra number	Probabilty	allocate
26	CARD	5	C26/$C$31	IF(B26>="1/6",0,((1/6-B26)*A32))
27	DIAB/ENDOC	-4	-  4/9	IF(C27>=1/6,0,((1/6-C27)*C31))
28	ELM	9	1	IF(C28>=1/3,0,((1/3-C28)*C31))
29	GASTRO	-1	-  1/9	IF(C29>=1/6,0,((1/6-C29)*C31))
30	RESPMED	0	0	IF(C30>=1/6,0,((1/6-C30)*C31))
31	Total	9

[sunny_talwar]

Not sure I understand this

also the allocate doesnt display minus beds ? just 0

Look the attached for removing specs

[qliklizzy]

i think it might be ok, not to show any minus beds as you cannot allocate a bed thats not there.

thanks you very much i will take a look at removing the specs.

you have been extremely helpful and very knowledegable - thank you