The logic you are using when calculating the % of insured cars for those months where one of the months does not have figures for Type A insurance is faulty. I have changed the formula so that the formula checks to see if there is a figure in the third month from the month of calculation, and if so it calculates Total insured cars/Total cars, otherwise it calculates Total insured cars/Total Cars of B Type and Forecast A. I guess this is what you were trying to do. For example, the formula cell for the month of September (J16), checks to see if there is a figure in the Type A cars cell of the month of November (L3). Since L3 is blank, it calculates total insured cars by total cars of B type (J8:L8)+total Forecast A (J15:L15). This formula assumes that if the Type A car field (Row 3) is populated in the third month, it is also populated in the first and second month. That means, if November column has a value in Row 3, the September and october months will also have a value in Row 3.

The worksheet with modified formula is attached.

Attached Files

Testbook1.xlsx (13.1 KB, 9 views)