View Single Post
 
Old 08-06-2012, 03:11 AM
Venky62 Venky62 is offline Windows 7 64bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

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
File Type: xlsx Testbook1.xlsx (13.1 KB, 7 views)
Reply With Quote