Conditional Formatting

[niting]

hie forum,


I have a value in column C against employee code in Column B. The column C contains the eligibility amount( for the whole year) for the employee code.


The employee is entitled to draw some amount against this eligibility every month and I add columns every month , say Col F for April, I for May and so on.


My problem is, I want to ensure that the cell is highlighted if the cumulative monthly withdrawal amount exceeds the eligibility amount as appearing in Col C. I have to also ensure that the withdrawal should not exceed the accrual of eligibility amount till a particular month. If that is the case, the cell should also get highlighted.


Pls help me out on this.


Thanks

niting

 

[Hui]

Niting


I think this will help

C2: USE CF formula =$C2<=Sum(F2:Q2)

F2:Q2 Use CF formula =sum($F2:F2)>$C2

 

[niting]

Hui,


Lil problem in the above formula. The columns D, E, G,H contains some other data, i.e. the relevant column appears every 3rd column from Column C.


Also, to determine the proportionate amount, say for full years is Rs. 15,000. Then till July, the amount payable is Rs. (15,000/12*4). So i also have to determine that the cumulative amount does not exceed the proportionate amount till a particular month.


I hope I had been able to explain my problem in a better manner.


Thanks

Nitin Gupta

 

[Hui]

So change the ranges:

C2: USE CF formula =$C2<=Sum(F2,I2,L2,O2,R2,U2,X2,AA2,AD2,AG2,AJ2,AM2)

F2:AM2 Use CF formula =sum(F2,I2,L2,O2,R2,U2,X2,AA2,AD2,AG2,AJ2,AM2)>$C2

 

[SirJB7]

Hi, niting!

An alternative formula might be:

=$C2<=SUMAPRODUCTO((F2:AM2)*(RESIDUO(COLUMNA(F2:AM2);3)=0)) -----> in english: =$C2<=SUMPRODUCT((F2:AM2)*(MOD(COLUMN(F2:AM2),3)=0))

Regards!