Formula error

[Dee]

Hi All,

I have 2 ranges one is Self & 2nd one is Manager. If average of self range is >=2 then L1,if it is >3 & <4 it is L3 & if it is <= 5 it is L3. same condition applies for Manager range also. For the value in self range the answer should be average of self range & Manager Review pending if there is input in Manager range then it should be just give the average of Manager Review. Below is my formula which have some issue.If input is given in both Self & Average range then it should give only average of Manager range.


=IF((E2:E18)>0,IF(AVERAGE(E2:E18)<=2,"L1",IF(AVERAGE(E2:E18)>4,"L3","L2")),IF((D2:D18)>0,IF(AVERAGE(D2:D18)<=2,"L1 Mgr Review Pending",IF(AVERAGE(D2:D18)>4,"L3 Mgr Review Pending","L2 Mgr Review Pending"))))


Thanks in advance,

Dee...

 

[oldchippy]

Hi,


Does this work for you?


=IF(SUM(D2:D18)=0,IF(AVERAGE(E2:E18)>0,IF(AVERAGE(E2:E18)<=2,"L1",IF(AVERAGE(E2:E18)>4,"L3","L2"))),IF(AVERAGE(D2:D18)>0,IF(AVERAGE(D2:D18)<=2,"L1 Mgr Review Pending",IF(AVERAGE(D2:D18)>4,"L3 Mgr Review Pending","L2 Mgr Review Pending"))))

 

[SirJB7]

Hi, Dee!


Could you please try this too?


=IF(SUM(E2:E18)>0,IF(INT(MIN(MAX(AVERAGE(E2:E18),2),5)-0.1)-1>0,"L"&INT(MIN(MAX(AVERAGE(E2:E18),2),5)-0.1)-1&" Mgr Review Pending",""),IF(INT(MIN(MAX(AVERAGE(D2:D18),2),5)-0.1)-1>0,"L"&INT(MIN(MAX(AVERAGE(D2:D18),2),5)-0.1)-1,""))


Regards!


@oldchippy

Hi!

230 vs. 239 characters, 3.77% of savings



Regards!

 

[Montrey]

@Sirjb7

LOL