Hello Everyone,

My query is as follows:
A1:25% D1:4
A2:25% D2:3
A3:25% D3:5
A4:25% D4:3

I would need to calculate the average value of B in percentage ,in cases when all of D's are there or one or more are missing and not taken under consideration. However , when some are missing the contribution percentage from A is distributed evenly between them. The values in D are from 0 to 10. The values in D will have different contribution and not always even.
This is a simple example .
Could you tell me If it Is posslbe to calculate such things. So far I have managed to do the formula where I Manually remove the factors that are not relevant in the specific case, however, Is it possible to be some kind more automated with a formula.
This is an example from my sheet:
=MAX(IF(OR(D21<6,D22<6,D23<6,D24<6,),((D21*20+D22*20+D23*20+D24*20)/1000-30%),(D21*25+D22*25+D23*25+D24*25)/1000),0)
as if when D is below 6 it should substract 30% of the total average percentage.