COMPLEX AVERAGE

[philgood34]

Hi everyboy

my challenge today :

I have these table (productivity analysis) with    VHT = CATTC / HEURES

I made TOTAL weighted average for VHT like this (thank's Sunny Talwar !) :

If(Dimensionality() = 0, Sum(VMS_CATTC)/Sum(Aggr(Sum(VMS_CATTC)/Sum(CIB_VHT), NCL_LIB)), Sum(CIB_VHT))

Correct !

Now is my issue :

i reload my table with some sectors who do'nt produce CATTC but with a productivity calculated with CATTC TOTAL

it does not match here :-((

So i want HEURES  to be calculated by 117127322/VHT if VHT <>0 , else HEURES will be calculated by CATTC/VHT

Obviously if VHT = 0, i don't want any calcul ... (to avoid DIV/0)

Crazy isn't it ?

Hope anyone 'll unterstand my issue

regards

[sunny_talwar]

What is this number here? 117127322

Is that a constant or is that coming from a calculation?

[philgood34]

it'is the sum of the CATTC  (here the sum of selectionned sectors beauté santé, liquides, epicerie , entretien) :

[sunny_talwar]

So based on your screenshot above, what are the expected value for the HEURES column?

[philgood34]

398105 expected

here is the workbook

[sunny_talwar]

May be like this

Sum(Aggr(If(Sum(VMS_CATTC) = 0, Sum(TOTAL VMS_CATTC)/Sum(VHT), Sum(VMS_CATTC)/Sum(VHT)), NCL_LIB))

[philgood34]

SO GOOD !  let me try to the total vht ..

thank's a lot again !

see you master

[sunny_talwar]

let me try to the total vht ..

Not sure I understand this?

[philgood34]

failed !

i tried this for the VHT TOTAL

If(Dimensionality() = 0, Sum(VMS_CATTC)/Sum(Aggr(If(Sum(VMS_CATTC) = 0, Sum(TOTAL VMS_CATTC)/Sum(CIB_VHT), Sum(VMS_CATTC)/Sum(CIB_VHT)), NCL_LIB))), Sum(CIB_VHT))

but i got error message "garbage after expression

a idea ?

[sunny_talwar]

I am not sure I follow.... What are you trying to do?